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

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).

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.

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

Read more: http://www.mytechmantra.com/LearnSQLServer/Fix-cannot-open-user-default-database-Login-failed-Login-failed-for-user-SQL-Server-Error/#ixzz3h4bqZ900
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook