SQL Database Error: Cannot Open User Default Database

Whenever a user establishes a database connection on SQL server, then the SQL database also creates a default database. If the user tries to connect to a computer which runs MS SQL but fails to specify login database while establishing the connection, then the computer uses the default database. However, sometimes, the default database is also not available? The MS SQL server is unable to establish connectivity in the absence of a default database, and the user experiences ‘SQL Database Error: Cannot Open User Default Database’.

Cannot connect to ServerName
Additional Information
Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Figure  – Cannot Connect to Server. Unable to Open User Default Database.

What Causes ‘Cannot Open User Default Database’ Error?

Following are some possible reasons causing the error:

1. The Login account is a member of multiple groups, and default database for one of the user is not connected when the user establishes a connection.
2. The database is in Suspect Mode or is part of Database Mirror.
3. DB ‘no longer exists’, ‘is offline’, ‘has been detached’, or ‘set to Restricted_User state’.
4. DB is in single user mode and the only available connection to the DB is already being used by some other process.
5. The Login account is not mapped to the user or access is denied.

How to Fix the ‘SQL Database Error: Cannot Open User Default Database’?

Try implementing the following workarounds to fix the ‘cannot open user default database error 4064’:

Workaround 1 – Connect to SQL Instance using an Available Database

Specify a valid (and an available) database in the connection string.

Workaround 2 – Change the Default User Database

If the default database is not available, try to log in with the user account who can modify logins and change the user’s default database with the one which is available during the time of connection. For changing the default db:

• Use sqlcmd utility for SQL Server 2005, 2008 and later versions
• Use osql utility for SQL Server 2000 and SQL Server 7.0

Steps to Change User’s Default Database in SQL Server 2005, 2008 and later versions

To solve ‘cannot open user default database login failed SQL 2008’ error, do the following:

Step 1: For Windows authentication with MS SQL to connect to the instance, type the following in command prompt:

sqlcmd E -S InstanceName d master

Step 2: For SQL server authentication with MSSQL to connect to the instance, type the following in command prompt:

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

Note: Name of the Placeholders are –

1. InstanceName – Name of SQL server 2005 instance to connect with
2. SQLLogin – SQL server login for which default database is dropped
3. Password – SQL Server Login Password
4. For SQLCMD prompt, type “ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName” and press Enter, AvailableDBName is a placeholder for the name of the existing database that is accessed by SQL Server login in the instance
5. Type GO at SQLCMD prompt followed by Enter button

Steps to Change User’s Default Database in SQL Server 2000 and SQL Server 7.0

Follow these steps in SQL Server 2000 to fix the ‘cannot open user default database login failed’ problem:

Step 1: Go to the Command prompt and type “C:\>osql -E -d master” followed by Enter.

Step 2: At the OSQL prompt, type “1>sp_defaultdb ‘user’s_login, “master” followed by Enter.

Step 3: For the second prompt, type “2>go” and click on Go button.

Workaround 3 – Deploy a Professional SQL Database Recovery Software

If the above workarounds fail to fix the error, use a professional software for SQL database recovery like Stellar Repair for MS SQL. 

The software is not version-specific, meaning the same software can be used to resolve the ‘cannot open user default database login failed’ issue in SQL 2008, 2012, 2014, 2016, and 2019 versions. Secondly, the software can fix the corruption errors where the built-in utilities fail.

Perform the following steps to resolve the error after downloading and installing the software:

Step 1: Launch Stellar Repair for MS SQL software. When the main software interface window opens, select the SQL database which is affected.

Step 2: Click ‘Repair’ to scan and repair the corrupt database.

Step 3: Verify the content from the preview as available on the screen.

Step 4: Save the repaired database on the same or different location. There are four different formats to save the repaired database.

After completion of these steps, the user will be able to access the database.

Conclusion

SQL Database error “Cannot open user default database” occurs when the user does not specify the database for connection and the default database is not available. Resolve this error by establishing connection to an instance of SQL Server. In most cases, the connectivity is not available, the SQL server remains inaccessible thereby increasing database downtime.

Prevent database downtime by using a reliable SQL database repair tool like Stellar Repair for MS SQL software. It helps repair SQL db (.MDF/.NDF) files to get database up and running in no time.