April 5, 2011 at 5:45 pm
I administer a group of SQL servers. I am giving my users' access thru AD Windows group logins. It becomes administratively easy to add or remove windows logins from that group login account.
In prod there are a few databases identified as confidential databases. I may need to deny some logins within that group to avoid access.
This is what I am thinking. Even though I give access to that confidential db for that group login - if I deny one Windows login (db_denydatareader) within that group, since SQL server works on least permissions, that user who has been denied should not be able to access that confidential database.
The other users should be able to access this confidential database. Is that correct.
Is there any other way to avoid access for a database for one windows login within a windows group account?
Thanks for the help!
April 6, 2011 at 11:09 am
db_denydatareader may not be enough depending on what level of access the AD Group affords them. If they have permissions to create or drop tables then db_denydatareader will not prevent that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2011 at 1:30 pm
You're going around it the long way, at least from an overall security perspective.
Windows groups should be created for the explicit access to these confidential databases. If an existing group is not sufficient because it contains user accounts that shouldn't have access, don't use it. It will create an auditing nightmare with the solution you're proposing.
This may even be a good case where in AD the groups are specifically named for the type of access being granted.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply