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
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
INSERT INTO AuditDb.dbo.ServerLogonHistory
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
DROP DATABASE AuditDB
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.
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
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 (http://blog.SQLAuthority.com)