This project has moved and is read-only. For the latest updates, please go here.

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 privileged access. It is an important part of a Separation of Duties framework.

Security Compliance

DBA Non-Sysadmin Group configuration helps to provide Separation of Duties for SQL Server. If you setup Security Compliance then DBA Non-Sysadmin Group configuration will always be implemented.

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 SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014
SetupNonSAAccounts FULL Yes Yes Yes Yes Yes
SetupNonSAAccounts WORKSTATION Yes Yes Yes Yes Yes
SetupNonSAAccounts CLIENT N/A N/A N/A N/A N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter ConfigNonSAAccounts can also be used.

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.

Manual Configure DBA 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.
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.
3) Set the following values, and then click the Add button:
Role name DBA_NonAdmin
Owner dbo

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.
5) Add the DBA_NonAdmin group to the following roles:

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

7) Click OK to save the changes.

Copyright FineBuild Team © 2013 - 2015. License and Acknowledgements
Previous Configure Sysadmin Accounts Top Configure SA Account Next

Last edited May 20, 2015 at 12:12 PM by EdVassie, version 8