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

Have you ever got and 17892 Error ?

Some extra prepared Junior was playing around and had created something that might lock out form your instance ?

Logon failed for login 'sa' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
  • Let's see how we can fix this ?

    • We can see from the start that a log on trigger is doing it's job !!! So in order to make use of a Dedicated Administrator Connection.

    Log in to your host where the Sql Server service is running and open a command line prompt and connect to SQL Server using sqlcmd and -A option and argument -d master which will directly enable me to log on to master database .

     C:adrian> sqlcmd -S LocalHost -d master -A
 1>
  • Find the trigger that holds the login
    •  C:Usersadrian>sqlcmd -S LocalHost -d master -A
SELECT
    SSM.definition
FROM
    sys.server_triggers AS ST JOIN
    sys.server_sql_modules AS SSM
         ON ST.object_id = SSM.object_id
         go

definition

CREATE TRIGGER TEST_JOB
ON ALL SERVER WITH EXECUTE AS 'sharepointMonitor'
FOR LOGON
AS
BEGIN
        DECLARE @statusJob VARCHAR(10)
        DECLARE @lb_Exit BIT
        SET @lb_Exit = 0;
        DECLARE Jobs CURSOR LOCAL FOR
                SELECT LastRunStatus
                FROM dbo.nag

    So here is the villain !!! Let's save the code and drop the trigger.(question why the trigger was created letter ! now free the Sql Server)

     C:Usersadrian>sqlcmd -S LocalHost -d master -A
 1>DROP TRIGGER TEST_JOB ON ALL SERVER
go

    Done , test the connection to your instance now!

    Copyright 2020. All Rights Reserved