Fix SQL Server Error 4064: Cannot open user default database. Login failed.

Today, I got Microsoft SQL Server error 4064 while I was trying to connect to my SQL Server Instance using my login id techyaz. Read this article to fix this SQL Server error 4064. The error text of this error is “Cannot open user default database. Login failed.” This solution is apply to both SQL logins as well as windows logins. The error details are given below:

Related Articles:

• Fix 15170: Logins owns one or more Agent Jobs
• How to fix error 15434: Could not drop login as the user is currently logged in
• Error Code 15174: Login ‘xxx\yyy’ Owns one or more databases(s). Change the owner of database(s) before dropping the login.

Microsoft SQL Server Error 4064

SQL Server Error 4064 generates because the default database set for your login has been dropped or becomes inaccessible by any reason. Whenever we create any SQL Server or windows login, we mention a default database. If you don’t mention any database during login creation then SQL Server set default database as master database.

You might also get this error on secondary replica of AlwaysON Availability Group because that database on secondary replica remains into inaccessible mode. So, don’t do any changes if you are getting this error in AOAG environment. You just need to connect to the primary replica because it is designed to keep secondary replica database into inaccessible mode.

Here, we had a database named “TechyazDB” that was set as default database for my login techyaz. This database was dropped by someone and when I tried to connect to SQL Server, it gave me below Microsoft SQL Server error 4064 because database was not there on SQL Server instance. Below is the screenshot of Microsoft SQL Server error 4064 that is clearly saying “Cannot open user default database. Login failed.”

Solution

There are two solutions to fix this issue. Either make your database online by restoring it or by anyway or change the default database of this login to master or any other database that is online on the instance. As you cannot bring your database online because you can’t connect to the instance so your first option is to connect to the instance.

If you have other DBAs or sysadmin accounts, you can connect using those accounts to SQL Server and change the default database for your login to master and then you can make database connection.

Let’s consider only you are the admin on this instance so how will you fix it. Follow below steps:

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 of above image. You will get below screenshot.

You can see Connect to Database option is set to default database that was TechyazDB for this login. Now we need to change this default to any accessible database. It will not allow you to select from drop down. If you will browse the database it will give you same error. You need to enter or type the database name. I typed as master database as shown in below image.

Now click on connect button to establish the database connection. This time you can connect to your SQL Server instance.

Now, it’s your wish whether you want to bring your database online first or you want to change the default database of your login. Generally, if database has been dropped as a planned activity then you need to change the default database of your login. Read below section to know how to change default database of your login.

Change default database of Login Name

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.

As you have connected to your SQL Server Instance. Expand Security Folder followed by Logins folder. Now double click on your login name or right click on login and choose properties tab. You will get below property window.

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.

Second method is T-SQL method. Run below ALTER command to set the default database of your login.

--Change techyaz with your login name.
ALTER LOGIN [techyaz] WITH DEFAULT_DATABASE = master

Below is the screenshot of this command.

Related Articles:

• Fix Error 15173: Revoke the permission(s) before dropping the login
• How to fix error 15141: The Server Principal owns one or more endpoints.
• How to fix error 15138 and Error 3729

Here, I have explained step by step solution to fix SQL Server error 4064: Cannot open user default database. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.