I had previously written two articles about an intriguing observation of triggers online.
If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.
Let us recreate the scenario first and attempt to solve the problem.
/* Create Audit Database */ CREATE DATABASE AuditDb GO USE AuditDb GO /* Create Audit Table */ CREATE TABLE ServerLogonHistory (SystemUser VARCHAR(512), DBUser VARCHAR(512), SPID INT, LogonTime DATETIME) GO /* Create Logon Trigger */ CREATE TRIGGER Tr_ServerLogon ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO AuditDb.dbo.ServerLogonHistory SELECT SYSTEM_USER,USER,@@SPID,GETDATE() END GO /* Dropping Database AuditDB */ /* Please note login to Server again will Produce Errors */ USE master GO DROP DATABASE AuditDB GO
After the database is dropped and we try to login again the following error will be displayed.
Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
This error could have been evaded if the Trigger was dropped prior to dropping database.
USE master GO DROP TRIGGER Tr_ServerLogon ON ALL SERVER GO
Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.
Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.
C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Tr_ServerLogon ON ALL SERVER
I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.
Reference : Pinal Dave (https://blog.sqlauthority.com)