In this article I will show you some options to recover from the error "Cannot open user default database.  Login Failed".

How does this happen?  This happens when a login has a default database set, and that database is deleted.  When you open any of the Microsoft database tools (like SQL Server Management Studio or Query Anaylzer), it automatically attaches you to the default database.  The problem is, that if that database has been deleted, you get the following error message.

There are a few things that you can do to recover from this.  You can login with a different account and change the default database for the effected login.  You can do this in one of two ways.  Via T-SQL and the SSMS interface.

Step 1) Right click the effected user and choose properties.

Step 2) Change the default database to a different database.  The default for all newly created users is master unless a different database is defined so that's the safest choice.

Now what you probably know already is that SSMS will turn around and execute SQL commands to do this.  So let's just show you now how to do this.

exec sp_defaultdb 'myuser', 'master'

But what if you don't have a different user that can login and change the account for you?  Or, you don't have an alternative tool that will allow you to specify the database at login time.  The command line tools for SQL Server will allow you to specify the database which you want to execute.  You will use the same command as above.  Let's go over it.

First open the command prompt.

Next, if you don't have the SQL Server binn folder in your %PATH% then you will need to navigate to the location where you installed the SQL Server client tools.  By default, it is "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn>" where 90 is the folder for the version of SQL Server installed.  Then, you will use the command line (osql.exe or isql.exe) to issue the command to change the default database.

Since the osql command line allows you to specify the -d option to specify the database, you can initiate the command in the master database (or any other database that you still have access to).

SQL Authentication

osql -Sservername\instancename -Umyuser -Pmyuser -dmaster -Q"sp_defaultdb 'myuser','master'"

Windows Authentication

osql -Sservername\instancename -E -dmaster -Q"sp_defaultdb 'myuser','master'"

NOTE: There will be no output since the sp_defaultdb
system procedure does not have any output for non-errors.

Now that you have changed the default database, you will be able to login to the database using the standard Microsoft tools with no errors.