Fix SQL Server Login Issue: Login failed for user 'Username'

In this article I will try to list all the steps which require to follow when you receive “SQL server error message 4064 - Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064”

Possible Cause: Above error message appears due to several reasons like database is dropped that is default database for some of the users, desired database may be corrupt, database is office, database may be renamed etc. When any cause is true then you will receive above error message.

Default Database: Before going to fix above error message, lets take a look at the default database. In SQL server management studio, whenever you connect to any SQL server database then by-default database is 'MASTER database'. It is selected by default and forces you to change from database drop-down list. It is very annoying situation. If you want to change your default database then follow below steps:

• Open SQL server management studio (SSMS)
• Point to the object explorer –> Security –> Logins
• Now right on the Logins and select properties
• A properties window will be appear
• Change your default database from MASTER to desired database
  
  

Note: You can also change the default database by running below stored procedure:

 Exec sp_defaultdb@loginame='TestUser', @defdb='Test'     

Resolution/Fix: This error message does not appears frequently, but if you have received then here is a simple fix for it. You can connect your MASTER database and then change default database. To do this you need to follow below steps:

• Open SQL server management studio
• Now point to the 'File' button at top-left and click on it
• Go-to connect object explorer

• Now type the user-name & password and click on the 'options>>' button as shown in above image.
• From connection properties tab, change the connect-to-database to master or any existing database name on your server. As I have discussed earlier, MASTER database is the default database by default.
• After setting the database name to connect-to-database, click on the 'Connect' button.
• You will successfully login in your system.
• Now run this statement through your username.

ALTER LOGIN [username] with Default_Database = yourdatabasename

• Error message resolved.