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