February 20, 2013 at 1:03 pm
Hello,
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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 20, 2013 at 1:38 pm
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
@kbriankelley
March 20, 2013 at 4:06 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply