How to fix SQL SERVER Error: 4064 – Cannot open user default database. Login failed. Login failed for user?

If someone tries to connect to a computer running MS SQL but forgets to specify the login database, the computer might use the default database. However, if the default database is not available, you'll get an error message saying, "Cannot open user default database. Login failed. Login failed for user 'UserName'."

There are a few reasons why this error might occur:

-> The database is in suspect mode.

-> It is set to emergency status or is part of a database mirror.

-> The default database is missing or has been set to offline, detached, or RESTRICTED_USER state.

-> The database doesn't have the login account mapped to a user, or the user has been denied access.

-> The database is in single-user mode, and the only available connection is already in use.

-> The login account may be a member of multiple groups, and the default database for one of those groups is not available at the time of connection.

Solutions to Fix the "Cannot Open User Default Database" Error

To fix the "Cannot Open User Default Database" error, follow these steps:

Log in with another user account that can modify logins and change the user’s default database during the connection. Specify a valid database in the connection string. If that doesn't work, change the default database.

Solution 1: Change the Default Database in SQL Server 2019 and Later Versions

Step 1: Click "Start," then click "Run," type cmd, and press "Enter." Depending on the type of login, use one of the following methods:

If using Windows authentication, type: sqlcmd -E -S InstanceName -d master

If using SQL Server authentication, type: sqlcmd -S InstanceName -d master -U SQLLogin -P Password

Note:

1. InstanceName is the name of the SQL Server 2019 instance you're connecting to.

2. SQLLogin is the login whose default database is missing.

3. Password is the login password.

Step 2: At the sqlcmd prompt, type: ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is the name of an existing database that the SQL Server login can access.

Step 3: At the sqlcmd prompt, type: GO and press "Enter."

Using SQL Server Management Studio

a. Launch SSMS and connect to the database engine with server name and login details.

b. Click on the "Options" tab.

c. Change the "Connect to Database" option to any accessible database.

d. Click "Connect" to establish the database connection.

Change Default Database of Login Name

After locating the database, the next step is to switch the default database assigned to the login. You can do this in two ways: using T-SQL code or using a graphical interface (GUI). Let's begin with the GUI method.

Step 1: Expand Security Folder and Logins Folder. Double click on your login name or right-click and choose "Properties."

Step 2: Change the default database to master and click "OK."

Step 3: Change the "Connect to Database" to any existing database on your server.

Using T-SQL

Execute the following T-SQL command to set the default database for the login:

ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = master

Note: Replace loginname with your login name.

Solution 2: Connect to a Different Database

If you're the Database Administrator and want to fix the issue, follow these steps:

Step 1: Open SQL Server Management Studio and enter your server details when the 'Connect to Server' window appears.

Step 2: Input the Server name, username, and password, then click the "Options" button.

Step 3: Look for the 'Connect to database' option. It shows the default database for the account "nicktestuser." Instead of using the drop-down menu, type the name of any other accessible database or the master database directly.

Step 4: Click the "Connect" button, and this time your account should log in successfully.

Solution 3: Use Reliable Software to Fix SQL Database Issues

If you've tried the steps above to repair your database and still can't open it, consider using professional software like Stellar Repair for MS SQL. This software works for various SQL versions (2008, 2012, 2014, 2016, and 2019) and can fix corruption errors that built-in utilities may not handle.

Here's how to use the software:

Step 1: Open Stellar Repair for MS SQL and choose the affected SQL database.

Step 2: Click on 'Repair' to scan and fix the corrupt database.

Step 3: Check the repaired content in the preview.

Step 4: Save the fixed database in the same or a different location, with four available formats.

Step 5: After following these steps, you should be able to access your database again.

This tool quickly recovers and fixes SQL server errors. Let's talk about some important features of SQL Data recovery:

-> It recovers different file parts like tables, stored procedures, views, programming elements, triggers, defaults, and functions.

-> It can repair damaged MDF and NDF files of SQL Server databases.

-> You can save the recovered data in the format of a SQL Server database.

-> There's a preview option to check the data before saving it.

Conclusion:

The "Cannot open user default database" error in SQL Database happens when the user doesn't pick a database for connection, and the default one isn't there. You can fix this by connecting to a SQL Server instance. If there's no connection available, the SQL server stays inaccessible, leading to more downtime for the database.

To avoid downtime, use a trustworthy tool like Stellar Repair for MS SQL. It helps fix SQL database files (.MDF/.NDF) quickly, getting your database back up and running.


Was this answer helpful?

« Back

chat