How to Fix MS SQL Error 4064/4062: Cannot Open User Default Database - Qiling  

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.

sql database cannot open the 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:

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

Tip
InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting.
SQLLogin is a placeholder for the SQL Server login whose default database has been dropped.
Password is a placeholder for the SQL Server login 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:

Step 1: Select the corrupted database for recovery

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

Step 2: Repair the corrupted database

Step 3: Export to the database or as scripts

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

Related Articles


Is this information helpful?     

What can we do to improve this information? (Optional)
Refresh Please enter the verification code!