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.

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!

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