Click here to Skip to main content
Click here to Skip to main content

SQL Server Logon Triggers Trouble

, 18 May 2010
Rate this:
Please Sign up or sign in to vote.
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...
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Dmitry Vitkovsky
Software Developer (Senior)
Russian Federation Russian Federation
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 18 May 2010
Article Copyright 2010 by Dmitry Vitkovsky
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid