"Login failed for User" in SQL Server - Reason I - Do you know It?

For security, i have created some users as required access of the databases in SQL instance.
After some days all the logins are working fine but for one login i got failed to connect SQL instance.

I have tried many times but failed ever time. I did not know it was happen.

After tried, tried i have finally check the properties for that login. Everything seems was fine access and all other stuffs. But when i have seen the default database associated that login and nothing assigned for the same.

This was because of the database assigned as default, that was deleted. So SQL server was not able to find default database for that login.

I am sharing my practical experience with following example.

First creating database and login.

CREATE DATABASE DefaultDatabase

GO 

-- Creating login with sysadmin access

USE [master]
GO
CREATE LOGIN [UserforDB] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'UserforDB', @rolename = N'sysadmin'

GO

Write now this created login have "master" database assigned as default. Now we are going to change to database created above.

USE [master]

GO

ALTER LOGIN [DefaultLogin] 
WITH DEFAULT_DATABASE=[DefaultDatabase], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF

GO 

You are able to login and connect SQL instance. Now we deleting the default database DefaultDatabase assigned, then we will try to connect instance again with that user.


DROP DATABASE DefaultDatabase


GO

Now try to login with UserforDB. It will give error as following, "Login Failed for User"

when you looked at the properties and see the default database, It will be a blank.

For the resolution we need to change default database to [master] or whatever exist and have enough access on database. Let's change it and then we will see as we will able to login and connect the instance with same user.

USE [master]

GO

ALTER LOGIN [UserforDB]
WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],0
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

GO

Finally we go for the resolution and you will connect the database instance now.