November 19, 2015 at 12:06 am
Hi.
Local Windows user got locked in sever side.. after unlocked that user can able to login server side but not able to login SQL Instance by using windows authentication.
It produce the error when connecting SQL Instance as below
--Microsoft SQL Server, Error: 18456
How to fix it? do we delete that user in SQL login and re-add it again?
Thanks
November 19, 2015 at 1:52 am
You need to look at the state part of the error message
2, 5 = Invalid userid
6 = Attempt to use a Windows login name with SQL Authentication
7 = Login disabled and password mismatch
8 = Password mismatch
9 = Invalid password
11, 12 = Valid login but server access failure
13 = SQL Server service paused
18 = Change password required
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
November 19, 2015 at 2:50 am
Thanks for reply...
I checked at Error log
Message
Login failed for user 'Servername\sqladmin'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Message
Error: 18456, Severity: 14, State: 5.
It means invalid user at SQL 2012 instance side and impact of unlocked user OS level.
Can we delete and add it again at SQL login in security.?
thanks
November 19, 2015 at 4:36 am
Query sys.server_principals for that name. If it's not there, that explains it. If it does exist, then you have to ask yourself if the Windows user got locked or if it got deleted and then recreated. If you recreate the Windows login in SQL and your server is still in the same domain, the login should be created with the same SID so the database user SIDs should match the login SID.
November 19, 2015 at 5:42 am
Sounds like an orphaned user to me. Check the database level security and the server level security in the GUI. Remove the database level security, then go to the server level and either add the user then add the database level in the USER MAPPING tab of the server level properties box or open the existing server level user and then add the database level in USER MAPPING.
It's entirely possible you'll have to drop and add it into both places to get it working, though, too. Sometimes bad mappings just stick until you delete everything.
November 20, 2015 at 12:41 am
I have verified by query select * from sys.server_principals, and that windows logins already exists.
USE [master]
GO
CREATE LOGIN [servername\sqladmin] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [servername\sqladmin]
GO
SID=0x010500000000000515000000017CF23F899DF198F4C91586E9030000
Pl. correct me, if I am wrong comments.
That windows login already have sysadmin permission then no need to mapping any database that user.
Thanks
November 20, 2015 at 5:23 am
SQL Galaxy (11/20/2015)
Pl. correct me, if I am wrong comments.That windows login already have sysadmin permission then no need to mapping any database that user.
Thanks
You are not wrong. Usually.
There are some instances where we've found that sysadmin accounts do need to have a DB level login with db_owner, but I can't remember what scenarios this covered.
That doesn't change the fact that you should probably check for the login on each database level (in case someone did add it) and remove it. Then possibly remove it from the server level and re-add it there if you're still having issues.
November 20, 2015 at 6:55 am
Brandie isn't the only one to experience these seemingly-random quirks with logins. There are times when a user mapped to a login just doesn't work, but I haven't been able to nail down the root cause. The SIDs match and everything looks fine, but it simply won't work. Drop and recreate it and voila! It works again.
November 20, 2015 at 10:46 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply