Resolve “Cannot Open User Default Database.Login failed” Error

I was trying to connect to the database when I received this error – Cannot open user default database.Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

My authentication mode is SQL Server and the user is a part of the Active Directory. AdventureWorks is set as a default database. If someone has an idea on how to fix error code 4064 login failed, then please let me know.

The error usually come when the database is no longer exists or somehow anyone dropped the default database or has been getting detached. As a result, users will get SQL Server Error 4064 Login Failed message.

Note: The user can take the help of manual methods(discussed below) to resolve this problem. But in case if you want a Quick solution to recover your crucial SQL database objects. User can take the help of SQL Database Recovery Software. 

Download Purchase Now

Therefore, in the upcoming section, we are going to explain efficient technique to fix this technical issue of Microsoft SQL Server.

SQL Server Error 4064 Login Failed – Potential Reasons

It is evident from above that there is the connection issue with Default database. It means either the database is invalid or lacking connect permission. Multiple reasons are responsible for this error code 4064. Here, we mentioned all of them.

1. It might be possible that the login account of SQL Server is associate with multiple groups, and default database for one of the user is not connected while a user login to the account.
2. Database has Suspect, Offline or Detached state.
3. It can be possible that SQL database is in a single user mode where other connection is already being used.
4. The login account is not getting mapped to a user.
5. In case if the database is a part of database mirror.
6. If the database is offline or in emergency state.

Workarounds to Fix “Cannot Open User Default Database.Login Failed – Error 4064” Issue

Before performing this method, you must specify a valid database in the connection string. To prevent an error when the user’s default database is missing or unavailable, log in as a user who can modify all the logins. After that, change the user’s default database with the current database.  Let us have a look how to do this:

Method #1: Resolve Error 4064 Login Failed in SQL Server 2005 & Later Versions

Users can use sqlcmd utility to change a default database in Microsoft SQL Server 2005. For this, the user need to follow the below steps.

Step 1 – Click the Start icon, choose Run, type cmd, and then press Enter.
Step 2 –  Users can use any of the workarounds, depending on the type of authentication that MS SQL Server login uses:

• If the SQL Server login uses Windows authentication to connect with an instance, execute the below command in prompt, and press Enter.

  sqlcmd E -S InstanceName d master

• If the SQL Server login uses SQL Server authentication to connect with the Database engine, then input the following command in the prompt.

  sqlcmd -S InstanceName -d master -U SQLLogin -P Password

You have noticed that InstanceName is the placeholder for the name of the SQL Server 2005 instance to which you are going to connect. MS SQLLogin is equivalent to the login whose database is missed or deleted and Password is a placeholder for the password associated with the login name.

Step 3 – For the sqlcmd prompt, type the given below command, and then press Enter:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is the placeholder for the name of an existing database that can be accessed by SQL Server login in the instance.

Step 4 – In the sqlcmd prompt, type GO inside the field and then click on Enter button.

Method #2: SQL Server 2000 & SQL Server 7.0

Users can avail the osql utility to change a default database in Microsoft SQL Server 2000 and SQL Server 7.0. Here we have mentioned the steps.

Step 1. Now, go to the command prompt, enter the following cmdlet and then, press Enter key.

C:\>osql -E -d master

Step 2. At the osql command prompt, type the following command and press Enter.

1>sp_defaultdb 'user's_login', 'master'

Step 3. At the second cmd prompt, type the following and then click the Go button.

2>go

Final Words

Users may encounter an error – Cannot open user default database.login failed sql server, when the SQL Server is unable to find the default database. To troubleshoot this situation, we discussed the reason behind this error and the workarounds to resolve error code 4064 in SQL Server 2019 / 2017 / 2016 / 2014 / 2012 / 2008 / 2005 and below versions.