Link to home
Start Free TrialLog in
Avatar of jwa082276
jwa082276

asked on

SQL Security / windows logins / AD groups

I am wondering what is a solution for this issue I am having in my environment.  Currently I have sql servers that host several dbs that are being used by different applications.  I grant access via AD groups b/c I do  not want to manage access at an individual windows login level due to the number of people who access dbs on servers.

The issue I am having is that if I have a user which I have placed in an AD group, for example, ADGroupA and ADGroupA  is given access, for example, to DatabaseA, but that user is also in an AD group called  ADGroupB which does not have access to DatabaseA then the user will NOT be able to access DatabaseA UNLESS I give that AD group called ADGroupB access to DatabaseA.  However, in reality the ADGroupB should not have access to DatabaseA.  This happens all the time  b/c we are hosting mulitple dbs that are supporting multiple apps and I have users who support both apps.

I am trying to find a solution around this issue wherein we have users in multiple AD groups, but the AD groups should NOT have access to the same dbs.

I hope this has made sense and if anyone has run into this issue before and knows of a workaround or fix I would really appreciate it.
Avatar of MrC63
MrC63
Flag of Canada image

This is the "triple state" syndrome.  Members of Group A must have access to certain databases, and members of Group B must have access to certain other databases.

The trick is that there are some members who should have access to both sets of databases.  So now you need a Group C, which would then be granted access to both Group A and Group B databases.

Essentially, you need a third A/D group, because you actually have three options: A, B, or Both (C) -- hence the term triple state.

The nice part is that it's easy to assign or remove a person, via A/D, into one of the three groups depending on what they should have access to.  In future, you may have to develop additional groups to accommodate further database restrictions.
Avatar of jwa082276
jwa082276

ASKER

So, do you feel that having a third AD group is really the only way around this?
ASKER CERTIFIED SOLUTION
Avatar of MrC63
MrC63
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial