Fix Microsoft SQL Server Error 4064 Login Failed

What SQL Server Login Failed Error 4064?

When default database is not available during the connection, the user will not be unable to connect to the database as a result an error code 4064 prompt on the screen.

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

Reasons of the Microsoft SQL Server Error 4064 Login Failed

When the error "cannot open user default database login failed error 4064" occurs, it may be possible that the default database for connection is not specified. There are many other possible reasons for this error. Some of them are stated below:

1. The database may be in Suspect Mode or no longer exists on the server.
2. It may also be possible that the database has been going into emergency mode.
3. It is also possible that the database has been set to RESTRICTED_USER state.
4. The database is in single user mode and it is already being used by any other user.
5. The user who is trying to connect with the database has not mapped the login account or user has been denied access.
6. It is also possible that the database is a part of mirroring or it could be that the database has been detached.

Fix Cannot Open Default Database Error 4064

Lets have a look at the steps to fix “login failed for the user Microsoft SQL Server Error 4064”.

1. Firstly, open the SQL Server Management Studio.
2. Now select the click on Security button. On Connect to Server window you will notice that there is no database assigned as the default database.
3. Go to the Connection Properties and specify a database as test_db and click on the Connect button.
4. After connecting to SQL Server instance you need to run the given T-SQL query to assign the login account for the new assigned default database.

Syntax:

Use master
    GO
    ALTER LOGIN [MonitorDB] WITH DEFAULT_DATABASE = test_db
    GO

Conclusion

In this article, we have discussed why "cannot open user default database login failed 4064" error occurs. Here, we have also discussed, how can we fix Microsoft SQL Server 4064.