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.
Write now this created login have "master" database assigned as default. Now we are going to change to database created above.
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.
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.
Comments
Post a Comment