?
Solved

SQL Security / windows logins / AD groups

Posted on 2013-06-05
3
Medium Priority
?
158 Views
Last Modified: 2013-06-11
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.
0
Comment
Question by:jwa082276
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Expert Comment

by:MrC63
ID: 39224274
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.
0
 

Author Comment

by:jwa082276
ID: 39224413
So, do you feel that having a third AD group is really the only way around this?
0
 
LVL 4

Accepted Solution

by:
MrC63 earned 2000 total points
ID: 39224627
Yes.  

Your only other option is to place a specific user in Group A (or perhaps Group B), and then manually assign this person with specific access levels where appropriate.  You've already acknowledged, and I fully agree, that this is not efficient.

The solution is to create one, or possibly even more than one, A/D groups.  Assign the appropriate SQL access to each group, and then add the users to one or more of the appropriate groups.

In future you may find that you need to create 4 or 5 groups (or more), but with the proper group definitions you would simpler control user access by placing a user in, e.g. 2 of the 5 groups to provide the appropriate access.  If the groups and group access are defined properly, it's much easier to assign a person to a group than it is to go through a list of SQL databases.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question