Thursday, February 3, 2011

Surprise with SQL Server Management Studio and default Login database

I have taken offline one of my databases on my development SQL Server. However it turns out that my login was set to use that database as its default. Now when ever I try to do anything in object explorer it tries to connect using my default database and fails. It is completely stupid. I think program should let user chose new value if default one is not valid anymore.

The only working solution I have found is the following:
1. connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to.
2. execute the following query to change default database of login
exec sp_defaultdb @loginame='login', @defdb='master'