November 30, 2011 at 6:52 am
I have kind of a strange request. I need the BUILTIN\Administrators group added to one of my DB Servers, I know this is not good at all but I got over ruled on the issue. The reason is that there are a couple of service accounts which need full access to the database server. Now my question is also included in this group is the Domain Admins group which has 3 individual nt logins assigned to it. I want to deny all but one of them from logging on to SQL Server.
If I explicitly add their logins to the SQL Server itself but check the box Deny next to "Permission to connect to database engine" will this stop them from being able to log in?
November 30, 2011 at 6:58 am
Yes.
Granting a permission removes DENY or REVOKE of that permission on the specified securable. If the same permission is denied at a higher scope that contains the securable, the DENY takes precedence. But revoking the granted permission at a higher scope does not take precedence.
November 30, 2011 at 6:58 am
I know this doesn't specifically answer your question, but you'd be better off adding the service accounts as explicit logins and granting them sysadmin the adding back the build-in group. The problem being that additional people or accounts can be added to that group and they would automatically inherit full admin rights within your SQL Server. I can't say that I've tested the scenario that you raised.
December 1, 2011 at 7:53 am
The best practice is to specifically add the service accounts. It is also recommended to avoid DENY wherever possible. Therefore, push back and refuse the group.
K. Brian Kelley
@kbriankelley
December 8, 2011 at 10:07 am
Dev (11/30/2011)
...the DENY takes precedence...
Except in cases where Ownership Chaining is involved.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply