Configure DBA Non Sysadmin Group
| Previous Configure Sysadmin Accounts | Manual Configuration | Configure SA Account Next |
|---|
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.
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.
The DBA Non-Sysadmin Group configuration relates to Process Id 5CC and is controlled by the parameters below:
| SQL Version | Parameter | FULL Build | WORKSTATION Build | CLIENT Build |
|---|---|---|---|---|
| SQL2019 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2017 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2016 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2014 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2012 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2008R2 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2008 | /SetupNonSAAccounts: | Yes | Yes | N/A |
| SQL2005 | /SetupNonSAAccounts: | Yes | Yes | 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.
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.
-
Set User Mappings to allow use of the db_datareader role in all databases
The /GroupDBANonSA: group is given dbDatareader rights in the model database.
This will mean it will automatically have dbDatareader rights in any other database that is created after this point. However, if a database is attached rather than created, the DBA must ensure that the /GroupDBANonSA: group has db_datareader rights in that database
-
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
-
Set the following values, and then click the Add button
Option Value Role name DBA_NonAdmin Owner dbo 
-
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
-
Add the DBA_NonAdmin group to the following roles
Role Name db_ssisoperator SQLAgentOperatorRole ServerGroupReaderRole 
-
Right-click on the instance and select Properties
Select the Permissions page, select the DBA_NonAdmin login and set the following values
Permission Action Alter trace Selected View any database Selected View any definition Selected View server state Selected 
-
Click OK to save the changes
Copyright FineBuild Team © 2013 - 2020. License and Acknowledgements
| Previous Configure Sysadmin Accounts | Top | Configure SA Account Next |
|---|