In my organization usually the application team gives us the change(it is fully processes based) to DBA to perform the work on Production for any activity.
One fine day the APP’s DBA team raised an change for creating new login(s) and granting data reader/writer permission for xyz database using t-sql queries.
For example ->Below is the similar query which he provided us(in this I have used my test server).
USE [master]
GO
CREATE LOGIN [ MU-Uday] WITH PASSWORD=N'uu', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorksDW]
GO
CREATE USER [MU-Uday] FOR LOGIN [MU-Uday]
GO
USE [AdventureWorksDW]
GO
EXEC sp_addrolemember N'db_datareader', N'MU-Uday'
GO
USE [AdventureWorksDW]
GO
EXEC sp_addrolemember N'db_datawriter', N'MU-Uday'
GO
So one of my friends he executed the query successfully and it completed successfully.
So he sent an mail to the app user to verify, seems he is busy or off he didn’t reply to that mail
Saying that he is able to login. but after 15 to 20 days he come back and said he is not able to login
Getting below error-
So we have checked many ways to resolve the issue but it didn’t work how ever same issue I have resolved in my local system .
To resolve :
Scan the errorlog :Identify what is the state of login failed in my case the state of the error login as mentioned below
Date 25/10/2011 23:23:52
Log SQL Server (Current - 25/10/2011 23:31:00)
Source Logon
Message
Login failed for user 'MU-Uday'. [CLIENT: <local machine>]
Date 25/10/2011 23:31:17
Log SQL Server (Current - 25/10/2011 23:31:00)
Source Logon
Message
Error: 18456, Severity: 14, State: 5.
So keeping state level in mind (before this go through the information about state) I resolved the issue.
State
1
Only state returned in SQL 2000. Seems to be catch all where no other states apply; for example Account Disabled shows up as State 1(2005).
2
Invalid Userid
5
SQL Account provided does not exist on SQL Server. (SQL2005)
6
Attempt to use a windows login name with SQL Authentication
7
Login disabled and password mismatch
8
Password mismatch
9
Invalid Password
For other state go through the Link :
so understood that SQL server not able to recognize the login because in our script if you have observed
there was an small space provided while creating login as mentioned below-
USE [master]
GO
CREATE LOGIN [ MU-Uday] WITH PASSWORD=N'uu', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
So I have decided to let try renaming the login from [ MU-Uday] to [MU-Uday] then try for
Login –you can use t-sql or GUI to rename :alter login like as mentioned below-
Use master
ALTER LOGIN [ MU-Uday] WITH NAME = [MU-Uday];
So I plan to connect one more time using SSMS-Wow Iam able to connect without any issues.
See below
Happy ending…..user able to login after resolving the issue.
No comments:
Post a Comment