Locking everyone out with Sql Server Logon Triggers

In one of my not-so-fine moments today, I was able to effectively lock everyone out of one of our Sql dev databases today thanks to a rogue logon trigger I set. Logon triggers, for those of you who haven't come across them, get executed by the server after authentication, but prior to anything else. I'm leveraging this feature to open all symmetric keys the user has access to for the sake of cell security.

However nothing ever goes right the first time, and when calling a certain stored proc as part of the trigger, I forgot to tell it which database that stored proc was in. That meant that everytime a user would log on, they'd get hit with an error, and wouldn't be able to connect.

If you already had a query window open with a connection to that database, you would be able to do something like:

disable trigger <name> on all server

But sometimes things aren't as perfect and you don't have a pre-existing connection. To get past this, you can fortunately use sqlcmd thusly:

sqlcmd -S servername -d master -Q "disable trigger <name> on all server"

Which will execute the same sql on servername.master to disable the trigger and let you and everyone back on.

Now, don't do that again!

posted @ Friday, December 14, 2007 3:37 PM

Print

Comments on this entry:

No comments posted yet.

Your comment:



 (will not be displayed)


 
 
 
Please add 4 and 6 and type the answer here:
 

Live Comment Preview:

 
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910