New Domain / New Groups to Create in AD, any suggestions?

  • In the next couple of months we are planning on consolidating our IT centers into one. The project plan is creating a new domain, security groups and users. Does anyone have any suggestions on what groups to create for the SQL servers? Is there a best practice to keep it fairly straight forward? I was thinking along the lines of groups based on rights. (i.e. xx_RO, xx_RO_EXE_TRA, xx_RW_EXE_TRA, xx_DBO, etc)

    xx_RO = Datareader permissions

    xx_RO_EXE_TRA = Datareader, Execute, Trace

    xx_RW_EXE_TRA = Datawriter, Execute, Trace

    xx_DBO = DBO

    Any suggestions are most appreciated.

  • I suppose it really depends on how granullar you want to get or rather how granullar your organisation wants it to be.

    For example, we have a DBA's group and SQL Service group, a SQL Agents group and a SQL Jobs group.

    The idea behind the DBA's is that we wouldnt need to play around with individual server/instance permissions when a new member joins (the drawback is anyone in this group has SysAdmin to all the SQL servers, so no room for newbies or testers etc).

    The Service groups have the relevant permissions needed to perform the role (ie, local disk, service, san drive and domain rights). The reason we split them out this way is so if we start getting errors pointing to a group or users in a group, we would have sort of idea of what was going wrong.

    Hope this is of some help.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • As Adam said don't create too many AD groups like Data Reader\ writer etc in AD, keep it simple

    SQL Service account Group for SQL Services

    SQL server Agent Group for SQL agent

    DBAs for DBAs

    You can create data reader\writer etc groups as the requests come in...

    EnjoY!
  • I certainly am trying to keep it as simple as possible. I want to use these groups across all of our environments. That said, this would included all types of IT folks (Dev, BI, reporting, etc)

    I created five groups with the following criteria:

    DOMAIN\DB_DR – DataReader

    DOMAIN\DB_DREX – DataReader, Execute

    DOMAIN\DB_DRDWEX – DataReader, DataWriter, Execute

    DOMAIN\DB_DRDWDDLEX – DataReader, DataWriter, DDL, Execute

    DOMAIN\DB_Administrators – sysadmin

    Is there anything I might have missed?

    Thank you for your input and any additional information is much appreciated.

  • It better to have SQL services domain account and SQL agent Domain account as well, it is not recommended run services under DBA's admin account.

    EnjoY!
  • Agreed. The above are Security groups not user accounts. Did I miss any permissions?

  • Did I miss any permissions?

    I think those groups should do it for now.

    EnjoY!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply