SQL server error 4064 triggers while connecting to Microsoft SQL Server. This generally happens when the default database is not available to complete the connection requests.
As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.
Let us today discuss the possible causes and fixes for this error.
What is SQL server error 4064?
Each user has a default database. It by default uses this database for login if no other database is explicitly specified. But, what happens if the default database is unavailable at the time of the connection?
This normally triggers the 4064 error message with the message below:
What are the causes of SQL server error 4064?
Microsoft SQL Error 4064 occurs when the default database for a user is dropped. Thus when the user tries to log in, this error shows up. This error will occur whenever the user’s default database is offline as well.
In general, the error 4064 triggers when the user default database is unavailable at the time of connection. It may happen when the database:
1. Is in suspect mode.
2. No longer exists.
3. Is in single-user mode and the only available connection is already being used by someone or something else.
4. Has been detached.
5. Has been set to the RESTRICTED_USER state.
6. Is offline.
7. Is set to emergency status.
8. Does not have the login account mapped to a user, or the user has been denied access.
9. Is part of a database mirror.
Additionally, this may also happen when the login account is a member of multiple groups, and the default database for one of those groups is unavailable at the time of connection.
How to fix SQL server error 4064?
To resolve this error message, we need to specify a valid, available database in the connection string. To prevent the error when the user’s default database is unavailable, log on as a user who can modify logins. Then, change the user’s default database to a database that’s currently available for a connection.
We can use the sqlcmd utility to change the default database. To do this, follow these steps:
- 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:
* 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* 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
Replace the InstanceName in the command above with the name of the SQL Server instance to which you are connecting. Likewise, replace SQLLogin with the SQL Server login whose default database has been dropped and Password with the SQL Server login password.
- At the sqlcmd prompt, type the following, and then press ENTER:ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
Replace AvailDBName with the name of the existing database that can be accessed by the SQL Server login in the instance.
- At the sqlcmd prompt, type GO, and then press ENTER.
Using SQL Server Management Studio
We can perform this same task via SQL Server Management Studio as well. The steps to perform it include:
- Launch SSMS and click on connect to database engine. Enter server name and login details for which you are facing issue.
- Now click on Options tab.
- You can see Connect to Database option is set to default database.
- Now we need to change this default to any accessible database. You need to enter or type the database name.
- Now click on connect button to establish the database connection. This time you can connect to your SQL Server instance.
- You can bring your database online first or can change the default database for the login. If the database is dropped you need to change the default database of your login.
Change default database of Login Name
Once we have found the database, next step would be to change the default database set for the login. There are two options to change the default database set to any login. One is by using T-SQL code and another is by using GUI. Let’s start with GUI method.
1. Expand Security Folder followed by Logins folder. Now double click on your login name or right click on login and choose properties tab.
2. You can see default database is showing as blank. This was the main issue because your database has dropped from the instance. Now change it to master database and click on OK button to proceed.
3. Now change the connect to database to any existing database on your server like master or msdb
We can use the Alter command to set the default database for the login using the T-SQL method.
ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = master
Replace loginname with your login name.
[Need any further assistance in fixing SQL errors? – We’re available 24*7]
Conclusion
In short, the SQL server error 4064 triggers while connecting to Microsoft SQL Server. This generally happens when the default database is not available to complete the connection requests. Today, we saw how our Support Engineers fix this error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
Summary:- >Many users encounter SQL Server Error 4064 while using the MS SQL database. This mistake occurs when you connect to a system that’s running Microsoft SQL Server, and you do not specify a login database, then the default database is used. So, this article suggests the best methods to solve this error related to the MSSQL database.
What is SQL Server Error 4064?
This error happens when the user database is set to the default database. When the user attempts to log in and find that the default database is dropped, then this error is shown as if the user “abcd” tries to log in to the database but fails as the database is set as default.
Lets us know the causes behind this type of error.
Valid Reasons for SQL Server error-
If the database is in suspect mode or the database is no longer exists. Then this blunder can take place. It is also possible that the SQL database has been set to the RESTRICTED_USER state. The database could be offline as well. If a user does not have the login account mapped, or the user has been denied access, then this error can also take place.
How to Get Rid of SQL Server Error 4064?
To get rid of the “Cannot open default user database login failed” error, different techniques need to be used depending on the server setting. There are manual methods explained below to solve database error. Then manual methods are only applicable for SQL Server 2005 and its later version. So, those users who are using the SQL server 2005 and its later version should follow the below methods.
There are two manual methods that quickly resolve this error:
- Use SQLCMD to Modify Default Database
- Use the GUI to Change Default Database
- Rectify Issues with Sysinfotools SQL Recovery
Method 1: Use SQLCMD to Modify Default Database
SQLCMD utility is used to modify the default database in SQL Server. So, to do this, follow the steps that are shown below:
- The first step is to click on Start and type Run in the search field.
- Then in the Run dialog box type cmd, and hit Enter.
- Deploy any of the techniques in the command prompt according to the sort of SQL Server login authentication you prefer:
- For Microsoft Windows authentication, Type this command “sqlcmd E -S InstanceName d master”
- For SQL Server authentication, Type this following command “Sqlcmd -S InstanceName -d master -U SQLLogin -P Password”
Then type the below-mentioned command in sqlcmd prompt and then hit the Enter key:
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE= AvailDBName
Where AvailDBName shows the database name that already exists. This database can further be used by the login of SQL Server while synchronizing with SQL database.
Method 2: Use the GUI to Change Default Database
- First, open the SQL Server 2005 login page and then click on Options.
- Next, modify the Connect to database status with the help of existing databases such as Master.
- At last, click on connect.
Method 3: Solve Error with SQL Database Recovery
You can follow the above important methods one by one to solve the database error. If you are facing some issues with manual methods there is another best way to get rid of this issue is SQL Database Recovery.
This software recovers quickly helps in removing the SQL server error. Let us discuss some important features of SQL Data recovery:
- Recovers file objects including tables, stored procedures, views, programmability, triggers, default, and functions.
- Corrupt MDF and NDF files of SQL Server database can be repaired by this.
- It can save recovered data either in SQL Server database format.
- The preview option is there to review the data before saving.
Last words
Manual and a direct method has been explained above to move out from the SQL Server error 4064. Users can use any of the above methods but if someone wants to resolve the issue quickly can opt for the direct method. As you don’t need to identify the corruption of SQL data manually, this tool will automatically solve your issue and you can easily recover corrupted MySQL Database.
When I want to connect to SQL Server 2008, I get this message:
Cannot connect to server.
Additional Information: Cannot open user default database. Login failed. Login fail for user ‘sa’. (Microsoft SQL Server. Error:4064)
How do I resolve this error?
asked Apr 4, 2012 at 11:21
0
This error (Microsoft SQL Server.Error:4064) occurs when the database which is dropped is default for some database user.
When the user tries to login and their default database is dropped the error shows up.
Cannot open user default database. Login failed. Login failed for user
‘UserName’. (Microsoft SQL Server, Error: 4064)
The way to resolve this is given here, summarized below.
The solution to log on is:
- From the Connect to Server dialog, click Options
- Change value of Connect to database: to any valid database on the server
The permanent solution to fix the server is to run SQL like:
ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
GO
Abacus
2,0111 gold badge18 silver badges22 bronze badges
answered Apr 4, 2012 at 11:52
painotpipainotpi
6,8401 gold badge37 silver badges69 bronze badges
3
In my case this was due to a user being a member of multiple groups, each group having its own login. All groups’ logins need to have their default database set to a database accessible to the user.
Other possible problems are listed at https://support.microsoft.com/en-us/kb/307864.
You can set each login with an invalid default database to have a default database of master (accessible to all logins):
alter login [loginname] with DEFAULT_DATABASE = master
answered Jun 23, 2015 at 7:21
Simon DSimon D
4,1305 gold badges39 silver badges47 bronze badges
The user default database is unavailable at the time of connection so that this error message will popup .SO check which server you tagged to that particular user to access whether it is online or offline mode.
answered Aug 23, 2016 at 4:58
BhanuBhanu
31 silver badge
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.
Download Now
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.
I unknowingly deleted SA user from the SQL Server 2012. Now i cannot be able to connect to the sever. It shows an error :
Cannot open user database. Login failed. Login failed for the user ‘username’.(Microsoft SQL Server, Error: 4064)
Now i don’t know what to do?
Help me please
Thanks
- sql-server
asked Aug 9, 2012 at 5:48
Erma IsabelErma Isabel
2,1578 gold badges34 silver badges64 bronze badges
2
-
Do you have access to the DB server, and an administrator account on that machine?
Aug 9, 2012 at 6:02
-
Sorry, I meant do you have a Windows user account that is an administrator, and can you log in / RDP to the machine?
Aug 9, 2012 at 6:24
2 Answers
answered Oct 26, 2012 at 13:18
answered Aug 9, 2012 at 6:43
Adam BAdam B
3,7553 gold badges31 silver badges42 bronze badges