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