Hey again!
I have a SQL login, and I want to prevent the login from logging into SQL Server.
From SSMS, I connect to the Database Engine, expand Security, Logins.
I right-click the login in question and select Properties. I select the Status page.
So I see I can deny, disable and lock out this login.
What is the difference between these three options?
Also, shouldn't the checkbox "Login is locked out" be checkable?
Currently, it's unchecked/disabled and I cannot alter this value.
I performed a cross-reference of the possible combinations:
SQL Logins:
FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
UserName (valid) Grant Enabled No 0
UserName (valid) Grant Enabled Yes 18486
UserName (valid) Grant Disabled No 18470
UserName (valid) Grant Disabled Yes 18470
UserName (valid) Deny Enabled No 18456
UserName (valid) Deny Enabled Yes 18486
UserName (valid) Deny Disabled No 18470
UserName (valid) Deny Disabled Yes 18470
UserName (garbage) Grant Enabled No 18456
UserName (garbage) Grant Enabled Yes 18456
UserName (garbage) Grant Disabled No 18456
UserName (garbage) Grant Disabled Yes 18456
UserName (garbage) Deny Enabled No 18456
UserName (garbage) Deny Enabled Yes 18456
UserName (garbage) Deny Disabled No 18456
UserName (garbage) Deny Disabled Yes 18456
(garbage) (garbage) Either Either No 18456
NT-Logins:
FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
DOMAIN\UserName N/A Grant Enabled N/A 0
DOMAIN\UserName N/A Deny Enabled N/A 18456
DOMAIN\UserName N/A Grant Disabled N/A 18470
DOMAIN\UserName N/A Deny Disabled N/A 18470
where @.@.ERROR
0 = Connects
18456 = Login failed for user 'UserName'.
18470 = Login failed for user 'UserName'. Reason: The account is disabled.
18486 = Login failed for user 'UserName' because the account is currently locked out. The system administrator can unlock it.
(Upon third garbage-password, the SQL-account is locked.)
|||A plain 18456 "login failed" message could mean
- the user is using an incorrect username
- the user is using an incorrect password
- the user isn't defined to the datasource
- the user is denied access.
Based on these findings, I like the "disabled" setting over "denied." If a user calls me with an 18470 message, I have some confidence in that the user connecting with a valid username and password.
No comments:
Post a Comment