I am using an SQL database where I have seen the database connection lost when connecting to the server many times. This query is most common when connecting to the database server. So, here in this tech guide, we will explain all about Microsoft SQL server error 4064 Cannot open user default database and also how it can be resolved. This guide comprises of reasons behind the database error 4064, solutions for this error and at last a third-party solution.
Every user has a default account-related database in SQL Server. Each time the user attempts to sign in without specifying a password, the default database remains active. But if the default database is not available, or the SQL Server does not locate the server, the issue will be shown with an error message.
Error:
“Cannot open user default database. Login failed.
Login failed for user “username” (Microsoft SQL Server, Error: 4064)
SQL Server Error 4064”
When you double-click the user (Expand Security-> Logins) you will end up seeing that Default User is not allocated a user as shown below.
Reasons for the occurrence of Server Error 4064:-
There are many more explanations for the error such as the unavailability at the time of communication of the user default server. It may be that the database is in suspicious mode or offline. There is also the possibility that it was set to or left disconnected from RESTRICTED USER. The number of causes could be the following:
- RESTRICTED USER: Limiting access to databases by general users is important when performing certain maintenance tasks, particularly in recovery situations. Limited access mode can be used in such circumstances to restrict connections to high-level users only.
- There is no longer any database: When there is no database present then it may result in server error 4064.
- The database is set to the stage of an emergency situation: When a database is placed in the state of emergency, three major changes are made to the database configuration:
- Makes the database READ ONLY
- Restricts access to representatives of the set server feature only
- Logging is disabled (as the database is READ ONLY)
- It operates in single-user mode: It shows that only one user can connect to the server.
Because of the above-mentioned reasons, Microsoft SQL server error 4064 occurs. So, to solve this blunder “cannot open default user database” we are going to explain various methods in detail.
Solution 1: Connect to Server
Specify the name of the server as TempDB for Connect to Server in the Connection Properties and press the Connect option to connect to the SQL Server.
Upon connection with the SQL Server Instance, execute the TSQL below to add a new default server to the account.
Use master
GO
ALTER LOGIN [MonitorDB] WITH DEFAULT_DATABASE = TempDB
GO
Solution 2: Connect to Different Database
You can log in to your account if you are the Database Administrator and switch the default server to the master server. Follow the steps below:
- Begin the Studio for SQL Server Management. When the wizard ‘ Connect to Server ‘ opens, enter the name, username, and password of the database. Tap on the Options tab here.
- Look “Connect to database” option which is connected to the user account’s default server. After this, you can type any database name that is accessible or write the master database name as well.
NOTE: Do not pick any other server by using the drop-down as it may send the error.
- Tap the Connect key to sign in successfully this time.
Solution 3: Change the Default Database
You can also use the login properties to change the default database to any other database. Follow the steps below:
- Go to Security Folder and expand it after connecting to your SQL Server case. Select the folder and go to your login name. Tap the name of the account right click on Property.
- You can see the ‘ default database ‘ option in the bottom part. If it is empty, this means that the account has not associated with your default folder. Type the name of the database that is accessible and then click on the OK button.
You can also execute the T-SQL command to alter your SQL account’s default server. Follow the instructions:
“ALTER LOGIN [username] WITH DEFAULT_DATABASE = master”
Methods mentioned above can be said to fixing Microsoft SQL server error 4064. But the problem arises when a database is accessible by the user. Then a third party utility comes named SQL Recovery that is capable to recover inaccessible files of any server. Also, it can easily recover damaged databases quickly.
Conclusion
This guide has been discussed all the effective measures for solving the server error 4064 in the SQL database. With manually and alternatively both the solutions have been described above. So, users can use any one of the options whether working step by step manually or directly go with SQL Recovery utility.
