November 3, 2010 at 4:13 am
Playing with security as part of my studying for 70-432, and I'm looking at creating Logins. The Status screen has two setting - Permission to connect to a database engine - Grant/Deny and Login Enabled/Disabled.
If I Deny permission to connect then an attempt to login produces error 18456. If I disable the login then I see error 18470, and the icon for the login in Management Studio changes to signify it is disabled.
However, the end result appears to be the same - the login cannot access the server. So why are there two methods of preventing access?
I've read the description for this screen in BOL, but it doesn't explain why there are two ways of doing this.
November 3, 2010 at 7:01 am
some of it has to do with the sysadmin role...
you cannot deny connect to a sysadmin, BUT you can disable a sysadmin login....
November 4, 2010 at 2:39 am
Short answer: Windows security groups which are logins.
You cannot disable a Windows group login since it represents multiple effective logins. You can DENY it, however. Typically we disable except in cases like this. We try to avoid explicit DENY permissions whenever possible, in keeping with practice for file system, Active Directory, and even SQL Server database permission best practices.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply