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.Top

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:

db_ssisoperator

SQLAgentOperatorRole

ServerGroupReaderRole

6) Right-click on the instance and select Properties. Select the Permissions page, select the
DBA_NonAdmin login and set the following values: