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


Jan 20, 2014

Introduction

In this article we will take a look at the steps which you need to follow when you receive “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)“ SQL Server Error message.

Error Message:

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

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

You may end up receiving this error when a Windows or SQL Server Login is not able to connect to the default database which is assigned to the user.

If you double click the user (Expand Security -> Logins) you would end up seeing no database being assigned to Default Database as shown in the snippet below.

SQL Server Login Properties Default Database Name is Missing

How to Fix “Cannot open user default database. Login failed. Login failed for user Error Message”

In the Connection Properties specify the database name as TempDB for Connect to Database and click the Connect button to connect to the SQL Server Instance.

In SSMS Connection Properties Specify Default Database as TempDB and Click Connect

Once you are connected to the SQL Server Instance execute the below TSQL to assign the login a new default database.

Use master
GO

ALTER LOGIN [MonitorDB] WITH DEFAULT_DATABASE = TempDB
GO

Conclusion

As a best practice it is always recommended to assign the default database to a user as TempDB. This database is recreated every time SQL Server is restarted and this way you can avoid getting these errors.

Share this Article


Geeks who read this article also read…




"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."






Follow @MyTechMantra on Twitter