Escaping from a runaway Logon trigger

, 2014-05-27 (first published: )

Most people know that logon triggers can be dangerous if you aren’t careful. And if you didn’t know, well, logon triggers can be dangerous if you aren’t careful. They can be really useful and are a great tool, but if you mess up you can REALLY mess up.

If you install the following code (and don’t, just don’t)

USE master
CREATE TRIGGER [Logon_Trigger] -- Trigger name
ON ALL SERVER FOR LOGON -- Tells you it's a logon trigger
ROLLBACK; -- Kill the connection attempt

You will end up with a situation where everyone sees the following message every time they try (unsuccessfully) to connect to your instance.


Now it may be nothing like that severe, you could end up only blocking all of your sysadmins or something like that.

So since there is always the possibility that something can go wrong what can we do to fix it?

First we need a connection. You can hope that you already have a connection open (maybe even the one that created the trigger in the first place). If so a logon trigger will not affect existing triggers so you should be good. If not then we need to open a connection using the DAC (dedicated admin connection). Logon triggers do not fire when connecting to the DAC so no triggers you create will block it. To connect to the DAC you can either use SQLCMD with the -A option or connect using a query window in SSMS. You cannot connect to the DAC using object explorer. In order to connect using a query window you will connect to ADMIN:ServerName\InstanceName. The “remote admin connections” setting will need to be set to 1 in order to connect from a remote machine. If it is not you will need to log into to the server directly and create your connection to the DAC from there.

Once you have the connection you need to know the name of the problem trigger. There are a couple of ways to do this. First if you wrote the trigger and know the name then you are golden. If not then you can run the following script:

SELECT * FROM sys.server_triggers
WHERE is_disabled = 0 -- If its a 1 it's already disabled 
				-- you don't care
  AND is_ms_shipped = 0 -- It was created by Microsoft you 
				-- don't care (hopefully)

This gives you a list of all of the server level triggers that are not already disabled and were not created by Microsoft. Now there may be triggers that are not logon triggers (server scoped DDL triggers for example) but are still server triggers. I’m honestly not sure how to tell the which are logon triggers and which are not without looking at the actual definition.

You can see the trigger definition by running this code:

SELECT [definition] FROM sys.server_sql_modules 
WHERE [definition] LIKE '%MyTestTrigger%'

Once you know the name(s) of the trigger(s) that are the problem you can do the following to disable/drop them.

You can then disable the trigger using the following command:


Or drop it using this command:


I HIGHLY recommend knowing how to stop a runaway logon trigger before implementing one. Otherwise you can get yourself into some trouble and the time to learn this stuff is not when your boss is standing over you because no one can log into the production box!

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, System Functions and Stored Procedures, T-SQL Tagged: code language, DAC, language sql, logon triggers, microsoft sql server, sql statements, sqlcmd, SSMS, system functions, T-SQL





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads