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
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.
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
| 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:
| Alter trace || Selected |
| View any database || Selected |
| View any definition || Selected |
| View server state || Selected |
7) Click OK to save the changes.
Copyright © 2013 Edward Vassie. License and Acknowledgements