17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

The error Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’." usually happens when try to login to the system after dropping the database.


Possible Reason

1. Drop the database after creating the trigger without dropping the trigger and try to login.

Let me explain the scenario with script example.

Example:

Consider a database MyTempDB.

[CODE]

use myTempDB
GO

--Create a table Called MytempTable

CREATE TABLE myTempTable
(val1 VARCHAR(100),
val2 VARCHAR(100))

GO

--Create Trigger

CREATE TRIGGER Trg_Temp

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO myTempDB.dbo.myTempTable ('XXX','xxx')

END
GO

[/CODE]

Dropping of myTempDB database


[CODE]

---Dropping of myTempDB database

USE master
GO
DROP DATABASE myTempDB --will throw Logon failed for login due to trigger execution

GO

[/CODE]


After executing the above code, we will get the error

Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’."

and we cannot login to the database.


Possible Solutions

1. Must use drop trigger before dropping the database

Example:

[CODE]

---Dropping of myTempDB database

USE master
GO

--Drop thhe Trigger

DROP TRIGGER Trg_Temp ON ALL SERVER

--Drop the database
DROP DATABASE myTempDB

GO

[/CODE]

2. If we don't give the Drop Trigger, we cannot login to the sql server database.

3. The fix is to use DAC (Using a Dedicated Administrator Connection to Kill Currently Running Query.)

4. Using windows authentication do the following

a. Connect to sql server using windows authentication

b. Goto command prompt

c. Type the following

C:\Documents and Settings\Manager> sqlcmd -S LocalHost -d master -A

DROP TRIGGER Trg_Temp ON ALL SERVER
GO

The -d option will enable us to directly login to the database