Graeme Hill's Dev Blog

SQL Server Management Studio and default databases

Star date: 2010.030

There is a really annoying bug (or at least what I would call a bug) in SQL Server Management Studio where you cannot login with a user whose default database does not exist. Even if you are already logged in and you rename the default database, you will automatically be logged out and will receive an error every time you try to login again. You can always login as a different user and change the default database, but if you only have access to the one account, you can change the default database using sqlcmd.

First, login with a different database in a command window:

sqlcmd -d master -S server -U username -P password

Then issue the following commands to change the default database:

1> ALTER LOGIN login_name WITH DEFAULT_DATABASE = master
2> GO

You should now be able to login with this account through management studio.