Good Question! This is one of those error messages that you will eventually encounter in SQL Server if you work with it enough. A login’s default database is a delicate thing that will prevent them from being able to logon if there is a problem. The default database defines which database the user will be automatically logged into if they connect and they don’t specify a database. Several things can cause the above error message. The most common is a login that does not have access to the database that is configured as its default. This is easily remedied; either set up a corresponding user for the login in their default database or change the default to a database to which the login has access. This error will also rear its ugly head if you drop a database that was configured as the user’s default database. There is no check when a database is dropped to make sure it won’t affect users, so dropping someone’s default database will leave them in the dark when it comes to logging in. As I said, this only happens if the user’s connection method does not specify a database. Many applications will provide the database name and will be immune to the issue. The fix is simple, make sure the logon has access to their default database and make sure the default database exists.
This problem can get a little worse if you break the default database of your system administrator accounts, such as your Windows account that has ‘sa’ level rights or the ‘sa’ login itself (and no other logins have the permissions to fix the problem). In this case, the fix is still simple, but you need to use an application that allows you to specify a database, such as SQLCMD. SQLCMD is a command-line tool that comes with SQL Server 2005 and 2008. When you login, you can specify a database to use that will override the default. So to fix your problem, login with SQLCMD as follows:
sqlcmd -S sql2008 –U sa -P password -d master
This will let you login and use the master database despite the incorrect default database. To fix your login simply run the following query from the SQLCMD command prompt (you can use any database that exists and to which the login has access, master is used as an example):
ALTER LOGIN sa WITH DEFAULT_DATABASE=master
That’s it, the next time you login your new default database of master, or whatever you specified, will be used.