SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

I had previously written two articles about an intriguing observation of triggers online.

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

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)

SQL SERVER - Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to 'master'. errortrigger

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
2> GO

SQL SERVER - Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to 'master'. sqlcmddac

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)

, , ,
Previous Post
SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution
Next Post
SQL SERVER – SQL Server Management Studio New Features

Related Posts

78 Comments. Leave new

  • Thank you for the info. But how do you create the logon triggers without having any users with login issues? I am trying to capture all the logins (distinct), current plus every time any users login to the instance(going through all the database) including login name, user name, time, host name, and database name. I have tried using logon trigger, and it kicked out users as well as myself from login in, so i had to delete the trigger. I have also used server audit, but didn’t get any effective results.( no db name, plus few hundred 1000 results which is not effective. Could you please suggest me a better way to deal with what i am looking for? Thank you.

    Reply
    • Why you were locked out? Logon trigger is useful and works well.

      Reply
      • Santosh Deshar
        November 7, 2018 1:56 am

        Pinal, i got the same error “logon failed for login due to trigger execution” and so did the other users. Is there a way to create logon triggers to capture the failed and successful logins without locking users ?

  • Hi Dave, I ran into the same issue so I connected to my server using sqlcmd with DAC, thanks to your excellent article.
    I don’t know the exact name of the trigger and when do a select name from sys.triggers a get 0 rows. Is there a way to get the name of the trigger ?

    Reply
  • Saved my life (and job) after locking myself and everybody else out…

    Reply
  • Thank you to this post.
    I’m dumb enough to create a whitelist using trigger, and forgot to add my user into the list. The result is I locked myself out of my own db.
    Your post save my life!!!!!

    Reply

Leave a Reply

Menu