SQL Login Failure

Reader wants to know what's keeping a user from logging into the default database.

• By Eric Johnson
• 11/05/2008

Q: A user logs on to SQL Server and receives the error, “Cannot open user default database. Login failed.” What’s wrong? A: This is one of those error messages that you'll encounter if you work with SQL Server long enough. The default database defines which database a user will be automatically logged into when connecting and that user doesn't specify the database to use. With that, several events can bring up the error. The most common is a login that does not have access to the database configured as the default. The fix is easily remedied: 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. Even if the default database is configured correctly, the error will rear its ugly head if you drop the connection to that database. There is no automatic check when a database is dropped to make sure it won’t affect users. Many well-written applications provide the database name and are immune to the issue. In this case, the fix is simple: Make sure the user logging in 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). Even 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 override the default database and specify another one via this SQLCMD code: sqlcmd -S sql2008 –U sa -P password -d master This will let you login and use the master database despite it being the incorrect default database. To fix the login, simply run this query from the SQLCMD command prompt (you can use any database that exists and to which the login has access; here, master serves 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.