Frequently, MS SQL server administrator faces SQL server error-4064 that is Microsoft SQL Database Login Failed Error 4064 while connecting with the SQL server database. The error is given below:
“Cannot open user default database.Loginfailed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)”
Therefore, this technical guide will provide you the best ways to solve Microsoft SQL server error 4064. You will know about the SQL error 4064, causes from which this error occurs and finally you will get the solutions one by one to solve this error.
This error happens when you connect to a system running Microsoft SQL Server, and when you do not specify a login database, the default database is used. So, this paper indicates the best techniques to resolve this mistake linked to the MSSQL database.
What is Microsoft SQL Server Error 4064?
The Microsoft SQL Database Login Failed Error 4064 or Microsoft SQL Server Error 4064 occurs when the user database is set to the default database. When the user tries to log in and discover that the default database has been dropped. As shown below:
SQL 4064 Causes:
- The database does not exist or in an offline, separated or Restricted User state.
- Database that comes with Suspect Mode or is accessible as a database mirror.
- The login account is not mapped to a user or users have been refused access to the database.
- The database is only accessible in a single user mode where another link is already used.
- The login account is a member of different groups, and the default database is for a single user who has not logged in when a user establishes a connection.
Read More | How to Fix Windows Fatal Error on Your System
Manual Solutions to remove this Error:
To get rid of the “Can’t open default user database login failed” mistake, distinct techniques have to be used depending on the server environment. Manual methods are described below to fix database error. Then manual methods only apply to SQL Server 2005 and its subsequent version.
Two manual methods to solve Microsoft SQL Database Login Failed Error 4064
- SQLCMD to Modify Default Database
- GUI to Change Default Database
Check out the details.
1. SQLCMD to Modify Default Database
Utility SQLCMD is used to alter the default SQL Server database. So, follow the measures shown below to do this:
- The first step is to click on the search field on Start and type Run.
- Then write cmd in the Run dialog box and click Enter.
- Deploy any of the command prompt methods depending on the type of SQL Server authentication you prefer:
- Type the following command and hit enter:
sqlcmd E -S InstanceName d master
- Command for SQL Server authentication.
- For Microsoft Windows authentication, type this command and press enter:
Sqlcmd -S InstanceName -d master -U SQLLogin -P Password
Then type in the sqlcmd prompt the command below and hit the Enter button:
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE= AvailDBName
Where AvailDBName displays the already existing database name. SQL Server login can also use this database while synchronizing with the SQL database.
2. GUI to Change Default Database
- First, open the login page for SQL Server 2005, then press
- Next, use current databases such as Master to alter the Connect to database status.
- Finally, press the connect button.
Professional Solution
To resolve the database mistake, you can follow the above significant techniques one by one. SQL Database Recovery is another best way to get rid of this problem if you face some problems with manual techniques.
This recovers software rapidly enables us to remove the mistake on the SQL server. Let’s talk about some significant SQL Data Recovery characteristics:
- It’s possible to repair corrupt SQL Server database MDF and NDF files.
- Recovers objects from files including tables, processes stored, opinions, programmability, triggers, default, and features.
- The preview option is available before saving to review the information.
- Either in the SQL Server database format, it can save retrieved information.
Final Words
We have already discussed a manual and professional solution to get rid of Microsoft SQL server error 4064. If a user fails to solve the error with the manual method then they may opt for a professional solution. SQL Database Recovery quickly helps to solve this error and you will find your database with proper working after using this method.