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:
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:-)
SQLpathy 