How to fix login failed with error cannot open user default db...

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

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

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.

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.