Cannot open user default database login failed ошибка 4064

If the user tries to connect to a computer which runs MS SQL but fails to specify the login database while establishing the connection, then the computer uses the default database. Sometimes the default database is not available. And you receive «Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)» SQL Server Error message, you cannot open user default database. 

Possible Reasons — Cannot Open User Default Database

The user default database is unavailable at the time of connection. The main causes are listed below: 

• The database is in suspect mode.
• It is set to emergency status or it is part of a database mirror.
• The default database (DB) is missing or has been set to the offline, detached or RESTRICTED_USER state.
• The DB does not have the login account mapped to a user, or the user has been denied access.
• The DB is in single user mode and the only available connection is already being used by someone or something else.
• The login account may be a member of multiple groups, and the default database for one of those groups is unavailable at the time of connection.

There are more possible causes that may lead to error code 4064/4062. But the way to repair the SQL Server error remains the same. Read on to see fix SQL database error 4064/4062 and repair the database.

Solutions to Fix the «Cannot Open User Default Database» Error

According to the causes above, the first thing to do is log in another user account that can modify logins and change the user’s default database that is available during the time of connection. Then, specify a valid database in the connection string. If this not working, change the default database.

Solution 1: Change the Default Database in SQL Server 2005 and Later Versions

You can use the sqlcmd utility to change the default database in SQL Server 2005, SQL Server 2000 and SQL Server 7.0. To do this, follow these steps:

For SQL Server 2005 and later versions

Step 1: Click «Start», click «Run», type cmd, and then press «Enter». Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

1. 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

2. 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

Step 2: At the sqlcmd prompt, type the following, and then press «Enter»:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.

Step 3: At the sqlcmd prompt, type GO, and then press «Enter».

For SQL Server 2000 and SQL Server 7.0

The steps to change the default database seem to be easier for SQL Server 2000 and SQL Server 7.0 users.

Step 1: At a command prompt, type the following and then press «Enter»:

C:>osql -E -d master

Step 2: Type the following and then press «Enter»:

1>sp_defaultdb ‘user’s_login’, ‘master’

Step 3: Type the following and then press «Enter»:

2>go

Solution 2: Use the SQL Database Recovery Software (Professional and Reliable)

In most of the cases, users will repair the database and access it again by following the steps above. What if you still can’t open it? If you are in such a situation, don’t hesitate to try EaseUS MS SQL recovery tool to repair your database.

This software can fix the corruption errors where the inbuilt utilities fail. And the same software resolves the problem in SQL 2005 and also on SQL 2007 and above. Follow the steps to perform a database repair:

Step 1: Select the corrupted database for recovery

• Launch EaseUS MS SQL Recovery.
• Select the corrupted database file by clicking «Browse» (the two dots) or «Search».
• After selecting the file, click the «Repair» button to start the Analyzing process. 

Note: To use this software, you need to stop the SQL Server service.

Step 2: Repair the corrupted database

• The software displays all the recoverable items in a tree-like structure. The items are shown in a left pane.
• Select the desired component to be recovered. From the window, click the «Export» button. 

Step 3: Export to the database or as scripts

• Choose to export the database objects to database or export the items as scripts
• If you choose «Export to database», enter the information required and choose the target database.

• A window appears up asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to begin the repairing process, click «OK». 

Note: Before clicking «OK», you need to restart the SQL Server service.

If the user tries to connect to a computer which runs MS SQL but fails to specify the login database while establishing the connection, then the computer uses the default database. Sometimes the default database is not available. And you receive «Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)» SQL Server Error message, you cannot open user default database. 

Possible Reasons — Cannot Open User Default Database

The user default database is unavailable at the time of connection. The main causes are listed below: 

• The database is in suspect mode.
• It is set to emergency status or it is part of a database mirror.
• The default database (DB) is missing or has been set to the offline, detached or RESTRICTED_USER state.
• The DB does not have the login account mapped to a user, or the user has been denied access.
• The DB is in single user mode and the only available connection is already being used by someone or something else.
• The login account may be a member of multiple groups, and the default database for one of those groups is unavailable at the time of connection.

There are more possible causes that may lead to error code 4064/4062. But the way to repair the SQL Server error remains the same. Read on to see fix SQL database error 4064/4062 and repair the database.

Solutions to Fix the «Cannot Open User Default Database» Error

According to the causes above, the first thing to do is log in another user account that can modify logins and change the user’s default database that is available during the time of connection. Then, specify a valid database in the connection string. If this not working, change the default database.

Solution 1: Change the Default Database in SQL Server 2005 and Later Versions

You can use the sqlcmd utility to change the default database in SQL Server 2005, SQL Server 2000 and SQL Server 7.0. To do this, follow these steps:

For SQL Server 2005 and later versions

Step 1: Click «Start», click «Run», type cmd, and then press «Enter». Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

1. 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

2. 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

Step 2: At the sqlcmd prompt, type the following, and then press «Enter»:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.

Step 3: At the sqlcmd prompt, type GO, and then press «Enter».

For SQL Server 2000 and SQL Server 7.0

The steps to change the default database seem to be easier for SQL Server 2000 and SQL Server 7.0 users.

Step 1: At a command prompt, type the following and then press «Enter»:

C:>osql -E -d master

Step 2: Type the following and then press «Enter»:

1>sp_defaultdb ‘user’s_login’, ‘master’

Step 3: Type the following and then press «Enter»:

2>go

Solution 2: Use the SQL Database Recovery Software (Professional and Reliable)

In most of the cases, users will repair the database and access it again by following the steps above. What if you still can’t open it? If you are in such a situation, don’t hesitate to try EaseUS MS SQL recovery tool to repair your database.

This software can fix the corruption errors where the inbuilt utilities fail. And the same software resolves the problem in SQL 2005 and also on SQL 2007 and above. Follow the steps to perform a database repair:

Step 1: Select the corrupted database for recovery

• Launch EaseUS MS SQL Recovery.
• Select the corrupted database file by clicking «Browse» (the two dots) or «Search».
• After selecting the file, click the «Repair» button to start the Analyzing process. 

Note: To use this software, you need to stop the SQL Server service.

Step 2: Repair the corrupted database

• The software displays all the recoverable items in a tree-like structure. The items are shown in a left pane.
• Select the desired component to be recovered. From the window, click the «Export» button. 

Step 3: Export to the database or as scripts

• Choose to export the database objects to database or export the items as scripts
• If you choose «Export to database», enter the information required and choose the target database.

• A window appears up asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to begin the repairing process, click «OK». 

Note: Before clicking «OK», you need to restart the SQL Server service.

Whenever a user establishes a database connection on SQL server, then the SQL database also creates a default database. If the user tries to connect to a computer which runs MS SQL but fails to specify login database while establishing the connection, then the computer uses the default database. However, sometimes, the default database is also not available? The MS SQL server is unable to establish connectivity in the absence of a default database, and the user experiences ‘SQL Database Error: Cannot Open User Default Database’.

Cannot connect to ServerName
Additional Information
Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Figure  – Cannot Connect to Server. Unable to Open User Default Database.

What Causes ‘Cannot Open User Default Database’ Error?

Following are some possible reasons causing the error:

1. The Login account is a member of multiple groups, and default database for one of the user is not connected when the user establishes a connection.
2. The database is in Suspect Mode or is part of Database Mirror.
3. DB ‘no longer exists’, ‘is offline’, ‘has been detached’, or ‘set to Restricted_User state’.
4. DB is in single user mode and the only available connection to the DB is already being used by some other process.
5. The Login account is not mapped to the user or access is denied.

How to Fix the ‘SQL Database Error: Cannot Open User Default Database’?

Try implementing the following workarounds to fix the ‘cannot open user default database error 4064’:

Workaround 1 – Connect to SQL Instance using an Available Database

Specify a valid (and an available) database in the connection string.

Workaround 2 – Change the Default User Database

If the default database is not available, try to log in with the user account who can modify logins and change the user’s default database with the one which is available during the time of connection. For changing the default db:

• Use sqlcmd utility for SQL Server 2005, 2008 and later versions
• Use osql utility for SQL Server 2000 and SQL Server 7.0

Steps to Change User’s Default Database in SQL Server 2005, 2008 and later versions

To solve ‘cannot open user default database login failed SQL 2008’ error, do the following:

Step 1: For Windows authentication with MS SQL to connect to the instance, type the following in command prompt:

sqlcmd E -S InstanceName d master

Step 2: For SQL server authentication with MSSQL to connect to the instance, type the following in command prompt:

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

Note: Name of the Placeholders are –

1. InstanceName – Name of SQL server 2005 instance to connect with
2. SQLLogin – SQL server login for which default database is dropped
3. Password – SQL Server Login Password
4. For SQLCMD prompt, type “ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName” and press Enter, AvailableDBName is a placeholder for the name of the existing database that is accessed by SQL Server login in the instance
5. Type GO at SQLCMD prompt followed by Enter button

Steps to Change User’s Default Database in SQL Server 2000 and SQL Server 7.0

Follow these steps in SQL Server 2000 to fix the ‘cannot open user default database login failed’ problem:

Step 1: Go to the Command prompt and type “C:>osql -E -d master” followed by Enter.

Step 2: At the OSQL prompt, type “1>sp_defaultdb ‘user’s_login, “master” followed by Enter.

Step 3: For the second prompt, type “2>go” and click on Go button.

Workaround 3 – Deploy a Professional SQL Database Recovery Software

If the above workarounds fail to fix the error, use a professional software for SQL database recovery like Stellar Repair for MS SQL. 

The software is not version-specific, meaning the same software can be used to resolve the ‘cannot open user default database login failed’ issue in SQL 2008, 2012, 2014, 2016, and 2019 versions. Secondly, the software can fix the corruption errors where the built-in utilities fail.

Perform the following steps to resolve the error after downloading and installing the software:

Step 1: Launch Stellar Repair for MS SQL software. When the main software interface window opens, select the SQL database which is affected.

Step 2: Click ‘Repair’ to scan and repair the corrupt database.

Step 3: Verify the content from the preview as available on the screen.

Step 4: Save the repaired database on the same or different location. There are four different formats to save the repaired database.

After completion of these steps, the user will be able to access the database.

Conclusion

SQL Database error “Cannot open user default database” occurs when the user does not specify the database for connection and the default database is not available. Resolve this error by establishing connection to an instance of SQL Server. In most cases, the connectivity is not available, the SQL server remains inaccessible thereby increasing database downtime.

Prevent database downtime by using a reliable SQL database repair tool like Stellar Repair for MS SQL software. It helps repair SQL db (.MDF/.NDF) files to get database up and running in no time.

I was trying to connect to the database when I received this error – Cannot open user default database.Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

My authentication mode is SQL Server and the user is a part of the Active Directory. AdventureWorks is set as a default database. If someone has an idea on how to fix error code 4064 login failed, then please let me know.

The error usually come when the database is no longer exists or somehow anyone dropped the default database or has been getting detached. As a result, users will get SQL Server Error 4064 Login Failed message.

Note: The user can take the help of manual methods(discussed below) to resolve this problem. But in case if you want a Quick solution to recover your crucial SQL database objects. User can take the help of SQL Database Recovery Software. 

Download Purchase Now

Therefore, in the upcoming section, we are going to explain efficient technique to fix this technical issue of Microsoft SQL Server.

SQL Server Error 4064 Login Failed – Potential Reasons

It is evident from above that there is the connection issue with Default database. It means either the database is invalid or lacking connect permission. Multiple reasons are responsible for this error code 4064. Here, we mentioned all of them.

1. It might be possible that the login account of SQL Server is associate with multiple groups, and default database for one of the user is not connected while a user login to the account.
2. Database has Suspect, Offline or Detached state.
3. It can be possible that SQL database is in a single user mode where other connection is already being used.
4. The login account is not getting mapped to a user.
5. In case if the database is a part of database mirror.
6. If the database is offline or in emergency state.

Workarounds to Fix “Cannot Open User Default Database.Login Failed – Error 4064” Issue

Before performing this method, you must specify a valid database in the connection string. To prevent an error when the user’s default database is missing or unavailable, log in as a user who can modify all the logins. After that, change the user’s default database with the current database.  Let us have a look how to do this:

Method #1: Resolve Error 4064 Login Failed in SQL Server 2005 & Later Versions

Users can use sqlcmd utility to change a default database in Microsoft SQL Server 2005. For this, the user need to follow the below steps.

Step 1 – Click the Start icon, choose Run, type cmd, and then press Enter.
Step 2 –  Users can use any of the workarounds, depending on the type of authentication that MS SQL Server login uses:

• If the SQL Server login uses Windows authentication to connect with an instance, execute the below command in prompt, and press Enter.

  sqlcmd E -S InstanceName d master

• If the SQL Server login uses SQL Server authentication to connect with the Database engine, then input the following command in the prompt.

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

You have noticed that InstanceName is the placeholder for the name of the SQL Server 2005 instance to which you are going to connect. MS SQLLogin is equivalent to the login whose database is missed or deleted and Password is a placeholder for the password associated with the login name.

Step 3 – For the sqlcmd prompt, type the given below command, and then press Enter:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is the placeholder for the name of an existing database that can be accessed by SQL Server login in the instance.

Step 4 – In the sqlcmd prompt, type GO inside the field and then click on Enter button.

Method #2: SQL Server 2000 & SQL Server 7.0

Users can avail the osql utility to change a default database in Microsoft SQL Server 2000 and SQL Server 7.0. Here we have mentioned the steps.

Step 1. Now, go to the command prompt, enter the following cmdlet and then, press Enter key.

C:>osql -E -d master

Step 2. At the osql command prompt, type the following command and press Enter.

1>sp_defaultdb 'user's_login', 'master'

Step 3. At the second cmd prompt, type the following and then click the Go button.

2>go

Final Words

Users may encounter an error – Cannot open user default database.login failed sql server, when the SQL Server is unable to find the default database. To troubleshoot this situation, we discussed the reason behind this error and the workarounds to resolve error code 4064 in SQL Server 2019 / 2017 / 2016 / 2014 / 2012 / 2008 / 2005 and below versions.

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;