Configure DBA Non Sysadmin Group - SQL-FineBuild/Common GitHub Wiki

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.

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

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

    ![dbDatareader Role](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/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

    ![New Role](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/NewRole.png)

  3. Set the following values, and then click the Add button

    Option Value
    Role name DBA_NonAdmin
    Owner dbo

    ![DBA Non-Sysadmin Role Name](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/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

    ![Create Role](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/CreateRole.png)

  5. Add the DBA_NonAdmin group to the following roles

    Role Name
    db_ssisoperator
    SQLAgentOperatorRole
    ServerGroupReaderRole

    ![DBA Non-Sysadmin Roles](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/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

    Permission Action
    Alter trace Selected
    View any database Selected
    View any definition Selected
    View server state Selected

    ![DBA Non-Sysadmin Rights](https://raw.githubusercontent.com/wiki/SQL-FineBuild/Commonhttps://github.com/SQL-FineBuild/Common/blob/master/Blob/SQLConfiguration/ConfigureAccounts/ConfigureDBANon-Sysadmin Group/DBANonAdminRights.png)

  7. Click OK to save the changes

Copyright FineBuild Team © 2013 - 2020. License and Acknowledgements

Previous Configure Sysadmin Accounts Top Configure SA Account Next