SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Need advice on designing SQL Server security using Active Direcory groups

Need advice on designing SQL Server security using Active Direcory groups

SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16906 Visits: 4141

For some reason I must not be Googling this correctly. I am looking for advice on how to best design and create Acitve Directory (AD) groups to use as the basis for Windows logins in SQL Server 2008 R2, with the emphasis on design.

I have found many links that discuss the technical ways to create AD groups and make logins in SQL Server, but nothing that addresses a methodology for how to design the groups in the first place. For example, should I create separate organizational units (OUs) for each server, or one OU pertaining to databases and then groups under that for each server's type of required access? I feel like I am engaging in rookie behavior when there is a tested method out there somewhere.

For example:
- I want to know *whether* I should ask our systems folks for multiple OUs before I do so.
- I don't know whether to arrange AD groups by server, such as a group name with the server name prefix, or by application, or some combination. I thought about DBSERVER_AppName_Admins, for example, but not sure if that is on the right track - perhaps just AppName_Admins under an OU of DBSERVER.

Thanks in advance for any help.

- webrunner

A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (57K reputation)

Group: Moderators
Points: 57404 Visits: 1917
Organizational Units:

Organizational Units (OU) are a structure wthin an Active Directory domain that allows for security delegation within Active Directory itself as well as segmenting GPO deployment. You don't need to worry about it at all with respect to SQL Server. It doesn't apply to what you're trying to do with SQL Server.

Windows Groups:

Windows groups should already be handled by your AD administrators. They should have a model for how to assign permissions, how and when groups are created, etc. You basically want to align with this model because if AD has been implemented right, the groups make sense based on your business operates. In your case you're simply going to need to understand how they build the group and what the appropriate groups are for you to assign permissions.

Groups by Server:

No, don't do this. From a conceptual level, you've got to think about groups providing access to resources across the environment, not just individual servers. You're operating on the wrong paradigm. For instance, the security group for the DBAs may be used for the following:

- Email enabled security group in Exchange.
- Grants read access to software repository (Windows file share) for SQL Server and related tool installations.
- Grants read/write access to department file share
- Grants appropriate change permissions for department SharePoint site
- Made a member of the sysadmin role on every SQL Server

See how this transcends individual servers?

K. Brian Kelley
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10715 Visits: 1211
There is absolutely no design aspect to it at all!

Users are assigned to OUs by your AD Team and are already in your Domain. The only thing you need to do is ask your AD Team to create your groups and assign specific users to those groups. Permissions that the users have inside your SQL Server are controlled by the SQL Server itself. All you need to do once you have created the login on the server for the group is assign permissions to said group as you would for a normal user! Nothing else!

The only thing you would really need to be aware of is that where a single user has multiple group membership, the permissions are cumulative unless an explicit DENY is issued. Otherwise there really is nothing to it!

Grouping by server is also really unecessary. Don't forget, you are going to create a login for your groups on each server to which they are to have access. No login = no access.

In summary: Create a basic AD group, assign users to that group, create a login for that group on your server and map the databases, assign permission, roles and privileges to that group as you would a normal user.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum