In SQL Server, each user has a default database associated with the account. Whenever the user tries to log in without mentioning a database, then the default database remains the active one. But, when the default database is not available, or the SQL Server does not find the database, then it gives an error message to showcase the problem.
Cannot open user default database. Login failed.
Login failed for user “RockySz.” (Microsoft SQL Server, Error: 4064)
SQL Server Error 4064
Here, the user ‘RockySz’ tries to log in to the SQL Server, but the Server do not find the default database and provide the error that it cannot open the default database and the login is failed.
Cause of the SQL Error 4064/4062
When the default database is not available during the connection, then it can be due to various reasons like the following:
- The database is no longer in SQL Server.
- The database is in suspect mode.
- The database is configured for the single user only and is already being used by another user.
- The database is detached from the user account.
- The database is in RESTRICTED_USER state.
- The database is in emergency status.
- The database is in an offline state.
- The user has been denied access.
- The database is part of the database mirror.
- The user account may be a part of many groups, and the database may be unavailable for one of those groups.
The Solution of The Error
There are two methods which you can use to rectify the error:
- Connect with a different database.
- Change the default database.
- Connect with a different database
If you are the Database Administrator, then you can log in to your account and change the default database to the master database. Follow the steps:
- Start the SQL Server Management Studio. When the ‘Connect to Server’ wizard opens up, input the Server name, username, and password. Here, click the Options button.
- Here, you can see the ‘Connect to database’ option for making the connection. It is set for the default database for the account RockySz. Now you can type the name of any other accessible database or can also write the name master database.
NOTE: Do not use the drop-down to select any other database because it can give the error.
- Click the Connect button, and this time the account will log in successfully.
- Change the default database
You can also change the default database to any other database using the login properties. Follow the below steps:
- After connecting with your SQL Server instance, go to Security Folder and expand it. Select the Login folder and go to your login name. Right-click the login name and click Properties.
- In the bottom part, you can see the option ‘default database’. If it is blank, then it means that your default database has been disassociated with the account. Type the name of an accessible database and click OK.
You can also run the T-SQL command to alter the default database of your SQL account. Follow the command:
ALTER LOGIN [RockySz] WITH DEFAULT_DATABASE = master
You can follow any method from the above two and rectify the 4064 error. But, your account will not access the default database file and if there is some important data saved in it, then it will also remain inaccessible. That’s why you should try to add the default database to your account or use Kernel for SQL Database Recovery software to retrieve the information from the default database and save it to another database.
Kernel SQL Database Recovery is a unique software which can access the corruption database files of SQL Server and recovers them instantly. You do not need to manually identify the corruption in SQL database as the tool can recover entire data from any kind of corruption. After recovering the file, you can save them in any database of any version of SQL Server. You can access the database file instantly after saving them to a live SQL Server. There is also an exclusive option to save data in an offline batch file at a safe location.