What to Do with SQL Server Error 4064?

Puran Kandpal | Modified: July 12th, 2018 | SQL |

“Yesterday, when I was trying to connect with my MS SQL Server 2005, I received an error message “Cannot open user default database. Login failed”. And, it terminates the authentication process abruptly and I had no idea, what happened to my SQL Server. Thus, I want to know that, Is there any command by which I can log into the SQL Server 2005. If anyone has any suggestion regarding this situation, then kindly suggest me”.

Most of the users are facing the same SQL Server error at the time of login. So, after considering this problem, in this write-up, we will describe the easiest solution that can be applied to both SQL Server login and Windows logins too. But, first, understand Why SQL Error 4064 arises at the time of connection with the database.

How to find Default database in SQL Server

In SQL Server, each user has a default database that is used at the time of login connection. But, due to some reasons, the default database may be dropped and no longer exists in a database. So, whenever a user provides credentials in the SQL login page and hit the connect button. The login process aborts abruptly.

Reasons Behind SQL Server Error 4064

There are number of possible reasons responsible for this error “cannot open user default database login failed error 4064”. Some of the major ones are listed below:

  • It may be possible that the database may be in Suspect Mode. In this situation, users are not able to access the MS SQL server database and no transaction will be processed.
  • It quite possible that default database may no longer present on SQL Server and can be dropped intentionally.
  • The default database has been set to Restricted_User. It means that database can be accessed by users with sysadmin, db_owner database role or dbcreator server roles.
  • It can also happen that the database used at the time of login has been detached.

How to Resolve SQL Database Error 4064

To troubleshoot the SQL Server 4064 login failed error, a user only requires to provide an existing database name that is available in the SQL database for the connection. The motive is to replace the user’s default database that currently not available for the connection with the existing database that is present in SQL database environment. So, next time when user’s attempts to login into the MS SQL Server 2005, no error message will be displayed.

These manual solutions are applicable to SQL Server 2005 and in the earlier version. There are two solutions by which users can easily get rid of this situation

  • Modify Default Database Via. SQLCMD
  • Change Default Database Via. GUI

Solution 1: Modify Default Database Via. SQLCMD

There is a requirement of SQLCMD utility and modify the default database in SQL Server. To do this, follow the steps that are shown below:

  • Initially, click on Start and type Run in the search field.
  • In the Run dialog box, type cmd and hit Enter.
  • According to the type of authentication of SQL Server login prefer, deploy any of the methods in command prompt:
    1. Type this command “sqlcmd E -S InstanceName d master” for Microsoft Windows authentication.
    2. Type this following command “Sqlcmd -S InstanceName -d master -U SQLLogin -P Password” for SQL Server authentication.
  • Note: InstanceName is equal to name of SQL Server 2005 instance to which the users are connecting. SQLLogin is equal to the name of SQL Server whose default database has been dropped. Although, Password signifies the login password of SQL Server.

  • Type the below-mentioned command in sqlcmd prompt and then hit the Enter key:
    ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE= AvailDBName
    Where AvailDBName denotes the name of existing database. This database further used by the SQL Server login while synchronizing with SQL Database.
  • Now, type Go and hit the Enter key.

Solution 2: Change Default Database Via. GUI

  1. Initially, open the SQL Server 2005 login page and click on Options.
  2. After that, modify the Connect to database status with the help of existing database such as master. After that, click on connect.

The Final Words

MS SQL error 4064 is a very familiarised error that generally occurs in almost every version of SQL Server. Therefore, in this write-up, we have discussed the reliable and easy methods that can resolve this issue nimbly.