Escaping from a runaway Logon trigger

3

May 19, 2014 by Kenneth Fisher

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
GO
CREATE TRIGGER [Logon_Trigger] -- Trigger name
ON ALL SERVER FOR LOGON -- Tells you it's a logon trigger
AS
ROLLBACK; -- Kill the connection attempt
GO

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

Logon_Trigger1

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:

DISABLE TRIGGER [MyTestTrigger] ON ALL SERVER

Or drop it using this command:

DROP TRIGGER [MyTestTrigger] ON ALL SERVER

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!

3 thoughts on “Escaping from a runaway Logon trigger

  1. […] the love of all that’s SQL, triggers are not toys! I’m not even talking about logon triggers or DDL triggers. I’m talking about plain old ordinary DML […]

  2. […] logon trigger. First of all let me say if you are using a logon trigger make sure you know how to log in and disable it if there are any mistakes. Basically what you are going to do here is create a logon trigger that will check for a specific […]

  3. […] are dangerous because they can stop anyone from being able to connect to the instance and it can take some extra work to get around them if they run amok. DML triggers are next on my be careful when using list. The problem is that these guys fire the […]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: