SQL Server Logon Triggers Trouble

Recently I had a problem: I was developing a logon trigger for SQL Server, and there was a bug in it. After deploying the trigger to the server he didn't allow me to login anymore. I was in panic and thought I would have to reinstall SQL Server.

Fortunately, I've found a solution how to overcome it.

Suppose you have created a logon trigger for SQL Server:

create trigger evil_trigger
on all server 
for logon
as
begin
	select * from evil_table;
end

You deploy it and SQL Server will not allow anyone to connect because the trigger cannot be executed:

Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

What to do? SQL Server will not allow to connect even as sa.

Here are the steps to fix this:

1. Stop SQL Server service.
2. Run your SQL Server in single-user mode from command-line:
c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe -m -s SQLExpress -c -f
of course, replace SQLExpress with your instance name. If your instance is default, remove -s SQLExpress at all.
3. Connect to SQL Server using sqlcmd:
sqlcmd -S (local)\SQLExpress -d master -E -X

4. Execute a command:

1>  drop trigger evil_trigger on all server;
2>  go
1>  exit

5. Precc Ctrl-C on SQL Server console, and answer Y (close it).
6. Start SQL Server service.
7. Enjoy :)

Yeah, and of course don't try to make it on the server, where you haven't got access to the console!

This has saved me a lot of work to re-install SQL Server.