Sql server 4064 ошибка

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»;

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.

Best Method To Resolve “ Cannot Open User Default Database Error 4064” In SQL Server

It is an obvious fact that SQL saves a default account-related database in its server automatically. While the user attempts to sign in without specifying a password, the default database remains active. But sometimes MS SQL login is not able to connect to the server, then the user encounters an error message on screen which is  “Error 4064 in SQL Server”.

Error Message

While using MS SQL user who has an account in the concerned server owns a database by default. Might be connection fail if the default database is also unavailable at the time of the linkage. Therefore, you encounter that a SQL Server error 4064 is received. 

The exact error message is written below and find the image of the error which is received by random users.

Cannot Connect to USER.

Cannot open user default database. Login failed.

Login failed for user “username” (Microsoft SQL Server, Error: 4064)

SQL Server Error 4064”.

It is very frustrating, while you have some urgent work on MS SQL and you encounter this error but you need not worry. In this article, we will offer you the best method to resolve SQL server error 4064 login failed for user. 

Possible Reasons for Receiving Error 4064 in SQL Server

There exist a lot more reasons for the error, such as due to unavailability of the user default database at the time of connection.  

Other variety of cause may be as follows that :

1. You might end up receiving this error when a Windows or SQL Server Login is not able to connect to the default database which is assigned to the user.
2. There is no longer any database present.
3. The login account may be a part of multiple groups and the default database for one of the groups is occupied at the time you want to create a connection.
4. The database doesn’t have an account login mapped to a user.
5. The user has been deprived of access.

Learn How To Resolve Error 4064 in SQL Server

Repairing this error is really easy so you can try again to login with that user but first before to Connect, you must go to the Options and you will be asked for the Connection Properties on the second tab of the login dialog box and you must enter the master database in the drop-down box, see the figures to understand quickly.

To get rid of the error we offers you an organic method, you need to just follow these mentioned steps:

• Initially, click on Option>> Button of “Connect to Server”.

• Now change the connect to database to any server like master.
• In the dialog box, select Connect and it will successfully log you in. After login to the system run the following statement for your username and it should fix your problem. Make sure to replace [test] with your username and master with your database name.

              ALTER LOGIN [test] WITH DEFAULT_DATABASE = master

The method mentioned above can be said to be the best one for fixing Microsoft SQL server error 4064. But the problem arises when a database is not accessible by the user. Then you can use an alternate method which is a professional tool named SQL Recovery, capable to recover inaccessible files of any server. Also, it can easily recover damaged databases quickly. 

Bottom Line

We have discussed both manual and alternate methods to resolve “Cannot Open User Default Database Error 4064” in the SQL server. But there are some limitations to the manual method as it cannot recover a SQL file that is inaccessible. Also, the manual method required a person who is technically sound. So we recommend you to use an alternate method which SQL Recovery software which is designed to restore the SQL database in one go. It is a direct method for recovery and no need for technical expertise. Hopefully, you found this article useful to resolve your problem of Error 4064 in SQL Server

Related Post