SQL server error 4064 triggers while connecting to Microsoft SQL Server. This generally happens when the default database is not available to complete the connection requests.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What is SQL server error 4064?

Each user has a default database. It by default uses this database for login if no other database is explicitly specified. But, what happens if the default database is unavailable at the time of the connection?

This normally triggers the 4064 error message with the message below:

What are the causes of SQL server error 4064?

Microsoft SQL Error 4064 occurs when the default database for a user is dropped. Thus when the user tries to log in, this error shows up. This error will occur whenever the user’s default database is offline as well.

In general, the error 4064 triggers when the user default database is unavailable at the time of connection. It may happen when the database:

1. Is in suspect mode.
2. No longer exists.
3. Is in single-user mode and the only available connection is already being used by someone or something else.
4. Has been detached.
5. Has been set to the RESTRICTED_USER state.
6. Is offline.
7. Is set to emergency status.
8. Does not have the login account mapped to a user, or the user has been denied access.
9. Is part of a database mirror.

Additionally, this may also happen when the login account is a member of multiple groups, and the default database for one of those groups is unavailable at the time of connection.

How to fix SQL server error 4064?

To resolve this error message, we need to specify a valid, available database in the connection string. To prevent the error when the user’s default database is unavailable, log on as a user who can modify logins. Then, change the user’s default database to a database that’s currently available for a connection.

We can use the sqlcmd utility to change the default database. To do this, follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:
   * If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:sqlcmd E -S InstanceName d master

   * If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

   sqlcmd -S InstanceName -d master -U SQLLogin -P Password

   Replace the InstanceName in the command above with the name of the SQL Server instance to which you are connecting. Likewise, replace SQLLogin with the SQL Server login whose default database has been dropped and Password with the  SQL Server login password.

3. At the sqlcmd prompt, type the following, and then press ENTER:ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

   Replace AvailDBName with the name of the existing database that can be accessed by the SQL Server login in the instance.

4. At the sqlcmd prompt, type GO, and then press ENTER.

Using SQL Server Management Studio

We can perform this same task via SQL Server Management Studio as well. The steps to perform it include:

1. Launch SSMS and click on connect to database engine. Enter server name and login details for which you are facing issue.
2. Now click on Options tab.
3. You can see Connect to Database option is set to default database.
4. Now we need to change this default to any accessible database. You need to enter or type the database name.
5. Now click on connect button to establish the database connection. This time you can connect to your SQL Server instance.
6. You can bring your database online first or can change the default database for the login. If the database is dropped you need to change the default database of your login.

Change default database of Login Name

Once we have found the database, next step would be to change the default database set for the login. There are two options to change the default database set to any login. One is by using T-SQL code and another is by using GUI. Let’s start with GUI method.

1. Expand Security Folder followed by Logins folder. Now double click on your login name or right click on login and choose properties tab.

2. You can see default database is showing as blank. This was the main issue because your database has dropped from the instance. Now change it to master database and click on OK button to proceed.

3. Now change the connect to database to any existing database on your server like master or msdb

We can use the Alter command to set the default database for the login using the T-SQL method.

ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = master

Replace loginname with your login name.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, the SQL server error 4064 triggers while connecting to Microsoft SQL Server. This generally happens when the default database is not available to complete the connection requests.  Today, we saw how our Support Engineers fix this error.