MS SQL Error 4064/4062: Unable to Open the User's Default Database

If a user attempts to connect to a computer running MS SQL without specifying a login database during the setup of the connection, the default database is used. Occasionally, the default database is unavailable, and you receive the following SQL Server error message: "Cannot open the user's default database. Login failed. Login failed for user 'UserName'. (Microsoft SQL Server, Error: 4064)" You cannot open the user's default database.

Possible reasons - Unable to open the default user database

The default database for the user is not available when connecting. Possible causes include:

• The database is in suspect mode.
• The database is set to emergency mode, or it's part of a database mirroring setup.
• The default database (DB) is missing, or it's set to OFFLINE, DETACHED, or RESTRICTED_USER status.
• The login account isn't mapped to a user in the database, or access to the user has been denied.
• The database is in single-user mode, and the only available connection is being used by another person or process.
• The login account might be a member of multiple groups, and the default database for one of those groups isn't available when connecting.

Though the reasons for error 4064/4062 could be more, the method to fix SQL Server errors remains the same. Read on to resolve SQL Database Error 4064/4062 and repair your database.

Solutions for the "Unable to Open Default User Database" Error

Because of the reasons mentioned above, the first thing to do is log in with another account that has permissions to modify logins, and change the default database for the user when connected. Then specify a valid database in the connection string. If that doesn't work, change the default database.

Solution 1: Change the default database in SQL Server 2005 or later

You can use the sqlcmd utility to change the default database for 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. Depending on the authentication mode that the SQL Server login uses, do one of the following:

If the SQL Server login connects to the instance by using Microsoft Windows Authentication, type the following at the command prompt, and then press Enter:

This command, sqlcmd -E -S InstanceName -d master, is used to connect to a SQL Server instance using the sqlcmd tool and specifies connecting to the "master" database. Here, "-E" indicates that Windows Authentication should be used, and "-S InstanceName" specifies the name of the SQL Server instance.

If the SQL Server login is connecting to the instance by using SQL Server Authentication, type the following at the command prompt, and then press Enter:

sqlcmd -S Instance_Name -d Master -U SQL_Login -P Password

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

Change the login name to SQLLogin, with the default database set to AvailDBName

Note AvailDBName is a placeholder for the name of an existing database that the SQL Server login has access to 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 easier for SQL Server 2000 and SQL Server 7.0 users.

Step 1: Type the following at the command prompt and press the "Enter" key:

c:\>osql -E -d master

Step 2: Type the following, and then press Enter:

Set the default database for user login to 'master'.

Step 3: Type the following, and then press Enter:

2>go translates to "2 is greater than go" in Chinese. Here, ">" is the greater-than symbol used in mathematics and programming, indicating that 2 has a greater value than 'go'.

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

Usually, by following the above steps, users can repair the database and access it again. What if it still fails to open? If you encounter such a situation, do not hesitate to try using the MS SQL Recovery tool to fix your database.

This software fixes corruption errors when inbuilt utilities fail to repair corrupted SQL database. The same software is effective for resolving issues in SQL 2005 and above versions. Follow the steps given below to repair the database:

Step 1: Select the corrupted database to be recovered

< li > Launch the MS SQL Recovery tool. < /li >< li > Select the corrupt database file by clicking on "Browse" (two dots) or "Search". < /li >< li > After selecting the file, click on "Repair" to initiate the analysis process. < /li >

Caution: You must stop the SQL Server service before you use this utility.

Step 2: Repair the corrupted database

The software displays all recoverable items in a tree view. These items are listed in the left pane. Select the components that you want to retrieve. In the window, click Export.

Select Database Objects

Step 3: Export to Database or Script

• Select whether to export database objects to a database or to export the project as a script.
• If you choose Export to Database, enter the required information and select the target database.

A window will appear, asking for the credentials to connect to the server and the destination where the recovery item should be saved. Click “OK” to start the repair process.

Note: The SQL Server service must be restarted before you click OK.