Previous Configure Sysadmin Accounts Manual Configuration Configure SA Account Next

Configure DBA Non-Sysadmin Group

FineBuild can configure the DBA Non-Sysadmin Group permissions that are needed by SQL Server.

The DBA Non-Sysadmin group allows the DBA to perform most day-to-day tasks without the need for priviliged access. It is an important part of a separation of responsibilities framework.

FineBuild Configure DBA Non-Sysadmin Group

The DBA Non-Sysadmin Group configuration relates to Process Id 5CB and is controlled by the parameters below:

Parameter Build SQL 2005 SQL 2008 SQL 2008 R2 SQL 2012
ConfigNonSAAccounts FULL Yes Yes Yes Yes
ConfigNonSAAccounts WORKSTATION Yes Yes Yes Yes
ConfigNonSAAccounts CLIENT N/A N/A N/A N/A

FineBuild also uses the following parameters to help Configure DBA Non-Sysadmin Group:
Prameter Default Value Description
GroupDBANonSA GBGGDBAN01 DBA Team Non-Sysadmin group

FineBuild will automatically grant the necessary rights to the DBA Non-Sysadmin group.
Top

Manual Configure Sysadmin AccountsDBA Non-Sysadmin Group

The following steps show what you would have to do for manual DBA Non-Sysadmin Group configuration. FineBuild does all of this work for you automatically.

1) Set User Mappings to allow use of the db_datareader role in all databases.

The GroupDBANonSA group will automatically be given db_datareader rights in any database that is created after this point, due to its rights in the model database. However, if a database is attached rather than created, the DBA must ensure that the GroupDBANonSA group has db_datareader rights in that database.
dbDatareader.png
2) In the msdb database, create the DBA_NonAdmin role to act as a container for permissions.

Navigate to Database Roles, right-click and select New Database Role.
NewRole.png
3) Set the following values, and then click the Add button:
Role name DBA_NonAdmin
Owner dbo

DBANonAdminName.png
4) Enter the DBA Non-sysadmin group name and click OK. When you return to the Database Role window, click OK to save the new role.
CreateRole.png
5) Add the DBA_NonAdmin group to the following roles:
db_ssisoperator
SQLAgentOperatorRole
ServerGroupReaderRole

DBANonAdminRoles.png
6) Right-click on the instance and select Properties. Select the Permissions page, select the DBA_NonAdmin login and set the following values:
Alter trace Selected
View any database Selected
View any definition Selected
View server state Selected

DBANonAdminRights.png
7) Click OK to save the changes.

Copyright © 2013 Edward Vassie. License and Acknowledgements
Previous Configure Sysadmin Accounts Top Configure SA Account Next

Last edited Apr 12, 2013 at 9:16 AM by EdVassie, version 2

Comments

No comments yet.