Home Forums SQL Server 2008 Security (SS2K8) Need advice on designing SQL Server security using Active Direcory groups RE: Need advice on designing SQL Server security using Active Direcory groups

  • 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