MS SQL Error 4064/4062: Cannot Open User Default Database

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 Qiling 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:

• Free Download
• Windows 11/10/8/7, 100% Secure

Step 1: Select the corrupted database for recovery

• Launch Qiling 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.

• Free Download
• Windows 11/10/8/7, 100% Secure

Related Articles

• How to Fix A Corrupted SQL Server Transaction Log File
• How to Restore Master Database in SQL Server 2008/2012 with or Without Backup
• Easy Ways to Fix Recovery Pending State in SQL Server 2014/2016/2017
• How to Fix SQL Database Page Level Corruption Automatically