Methods to Fix SQL Server Error 4064 Login Failed For User [Guest Post]

This post is written by Andrew Jackson

Overview

SQL Server Database helps users to create their own databases where they can store or retrieve data whenever required. Sometimes, user may encounter error while connecting to the database and it displays an error “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)” This SQL Server Error message appears when SQL Server Login is not able to connect to the default database. The post will be discussing about the SQL Server Error 4064 and the way to resolve it.

SQL Server Error 4064 Login Failed For User

Each user that has an account on the SQL Server belongs to a database by default. When user tries to connect to a PC running SQL Server without specifying login database, the default database is used. However, if the default database is unavailable at the time of connection, the SQL Server Error 4064 will be displayed. 

Possible Causes of the SQL Server Error 4064

Some of the causes for the unavailability of the database are as follows:

• DB is in single user mode & only available connection is used already.
• When your DB no longer exists 
• Database is in suspect mode 
• DB has been detached 
• Database is offline
• DB is set to emergency status
• Does not have login account mapped to the user
• User trying to login has denied access.
• DB is part of a Database Mirror

How to Fix the Error 4064

One solution to avoid the error when the user’s default database is unavailable, is to logon as a user that can modify logins and change the user’s default database to a database that is currently available for a connection.

Steps for fixing the error 4064 are as follows:

- Before login to the user’s database, go to Options that is in right corner of the login window.

- Go to Connection Properties Tab of the login dialog box, enter the ‘master’ database in dropdown box changing default database to master, and click on Connect.

After successful login in the system, type the following TSQL command for your username and database.

User must make sure that they changes [test] with their own username and master with their database name. 

Alternative Method for Resolving Error Message 4064

• In SQL Server 2000 & SQL Server 7
  
  OSQL Utility can be used to change the default’s database by following the steps:
1. In the command prompt window, type the following ‘C :\> osql -E -d master’ and press Enter.
2. Type ‘1>sp_defaultdb 'user's_login', 'master'’ at the osql prompt and press Enter
3. Type ‘2>go’ at the second prompt and press Enter
• In SQL Server 2005 & later versions
  
  SQLCMD utility is used to change the default database server by following the steps below:
1. Go to Start -> Run. Type cmd and press ENTER.
2. SQL Server Login can use either Windows authentication typing the following in command prompt ‘sqlcmd –E -S InstanceName –d master’ or SQL Server Authentication with ‘sqlcmd -S InstanceName -d master -U SQLLogin -P Password’
   {InstanceName =Name of SQL Server Instance to which user wish to connect
   SQL Login=Server Login whose database created by default has got dropped
   Password=SQL Server Login Password}
3. Type ‘ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName’ at the sqlcmd prompt and press ENTER
4. Now, Type ‘GO’ and press ENTER.

Conclusion

The post is aimed to guide users in resolving one of the most common errors faced by users while trying to connect to the database that is unavailable at the time of connection. The possible causes of the SQL Server Error 4064 login failed for user are discussed as well. It further defines solutions to change the unavailable database to any valid database on the server in order to resolve the 4064 Error.