Wednesday, April 29, 2009

SQL Server lockout

If you find yourself spending almost all of your time in SQL Server Management Studio working with the same database, it is kind of handy to set your login's default database from master to that particular database.  That way, when you fire up SSMS, the query window is ready to get to work, and you run less of a risk of adding yet another eronious table to master.  99% of the time this is pretty good.

However, if you do something like ... oh, say ... bring that database offline to dump connections so you can do a restore, and then you try to do .. oh, maybe ... anything else in SSMS, you might find that you are pretty much hosed.

Here's a quick fix:

- fire up SSMS, but don't log in yet
- click 'options>>' on the login screen
- change your default database to master
- proceed with the login
- run the following script, changing your login name:  alter login [domain\user] with default_database = master
- bring the offline database back online
- proceed wreaking havoc on your server

I'm not saying this has ever happened to me.  I'm just passing along hypothetical help to all my anonymouse friends on the interwebs.