ERROR:17892 Database login issue due to logon-trigger

I was working with Logon triggers and was stuck-up in a very interesting issue. Tried to create one trigger named “Logon_trigger” which writes auditing information in audit table in DB named “TestDB”. Once I finished my testing, I dropped the database [TestDB] without dropping the trigger. Unfortunately when I tried to connect to SSMS after that, I got the following error:


Thought of connecting thru DAC to drop the trigger and rectify the issue. Interestingly, was not able to connect thru DAC and got the following error:

DAC error

Here is how I rectified the issue:
Connected to the SQL Server Configuration Manger: Start >> All Programs >> SQL Server 2008 R2 >> Configuration Tools >> SQL Server Configuration Manager
Added a trace flag to enable DAC (-T7806), as shown in the screen below and then re-started the service:


Tried to connect using DAC again and execute below commands:


Connected using SSMS successfully this time:-)


One comment

  1. KA · November 13, 2014

    WOW just what I was searching for. Came here by searching for backlinko twitter


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s