Connecting to SQL Server with a Bad Logon Trigger

By:   |   Comments (7)   |   Related: > Triggers


In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity.  One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?


If you created a trigger for LOGON and you have some bad code in the trigger, when you attempt to log on, you're going to get an error similar to the one shown in Figure 1.

Figure 1:

kbk ConnectingWithBadLogonTrigger01

The bad execution of the logon trigger is causing the logon attempt to fail.

For instance, the following code is designed to fail if the trigger is created:

    INSERT INTO BadDB.dbo.SomeTable VALUES ('Test');

There is no database known as BadDB and that means there is also no table within BadDB known as SomeTable. As a result, any normal attempts to logon the server will fail because the trigger is referring to a non-existent object. In order to correct this, you'll either need to:

  • Use an existing established connection that has the appropriate rights.
  • Use the Dedicated Admin Connection (DAC) to connect to SQL Server

If you have an existing connection that has the ability to drop or disable the trigger, use that existing connection to correct the problem. But in the likely event that you don't, you will need to rely on the DAC.

By default, the DAC is set up to only be available on the local server. That means you'll need to connect either by logging onto the computer locally or by using another means such as Remote Desktop. Once you're logged on, you can either use SQLCMD or SQL Server Management Studio (SSMS).

If you use SQLCMD, you'll want to specify the -A switch to connect using the DAC. If you're connecting via SSMS, be sure to do so by specifying ADMIN: before the name of the server, such as in Figure 2.

Figure 2:

kbk ConnectingWithBadLogonTrigger02

The reason this works is that SQL Server minimizes the checks and resources for a connection via the DAC. This was done to give DBAs a "back door" for when one or more runaway processes is consuming a SQL Server such that a normal login isn't possible. One of the things SQL Server does not do when connecting via the DAC is execute any logon triggers. As a result, you can use the DAC and you won't be blocked by the bad trigger. Then you can disable or delete the trigger as necessary.

For instance, once connected via the DAC, I could execute the following command to get rid of the trigger entirely:

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, July 12, 2012 - 5:15:56 AM - Phil Back To Top (18462)

Is it possible to use Powershell to delete a logon trigger that has locked down a DB

Friday, September 4, 2009 - 6:30:20 AM - tgerade Back To Top (4007)

I got it!

I had forgotten to put the "on ALL Server" at the end of my drop statement.

Thanks so much everyone.


Friday, September 4, 2009 - 6:25:10 AM - tgerade Back To Top (4006)


Thanks for your response.

Msg 3701, Level 11, State 5, Server CISDEVMST01,  Line 1
Cannot drop the trigger 'Trigger_ServerLogon', because it does not exist or you
do not have permission.


Friday, September 4, 2009 - 6:23:48 AM - ALZDBA Back To Top (4005)

It is strange sa cannot drop that trigger, however maybe just disable it to bypass your original problem.

and search for a solution if things are back running as normal.

Friday, September 4, 2009 - 6:19:36 AM - K. Brian Kelley Back To Top (4004)

Can you post the exact error message you receive when trying to delete the trigger?

Friday, September 4, 2009 - 6:08:18 AM - tgerade Back To Top (4003)


I tried the tip and did end up with the Bad login.  However, when I connect DAC as sa it states I do not have permission to delete the trigger.   Please help if you can.

Thank you.


Monday, November 24, 2008 - 1:17:01 AM - ALZDBA Back To Top (2263)

Your article describes exactly the issue I ran into and how i undid the issue using the DAC.

 My new solution uses event notifications, because it is just a logging application that should not block anything in real time.

I posted my little but drastic mishap at SQLserver central to be published in the near future. 


get free sql tips
agree to terms