March 9, 2010 at 4:45 am
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.
March 10, 2010 at 8:35 am
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
March 10, 2010 at 8:56 am
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...
March 12, 2010 at 12:56 pm
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.
March 12, 2010 at 4:28 pm
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.
March 12, 2010 at 8:30 pm
Agreed. The above are Security groups not user accounts. Did I miss any permissions?
March 12, 2010 at 9:43 pm
Did I miss any permissions?
I think those groups should do it for now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply