Sql-server – Rights to grant for controlling an Availability Group

availability-groupspermissionssql server

We are having the following setting:

For an ERP Test / Accept SQL Cluster we want to follow the least privilege principle to give the external engineers rights on the SQL Server. We are running SQL Server 2016 in a cluster with two nodes. Synchronous commit, readable secondary on true. The engineers are added to an AD group. We grant permissions to that AD group.

The engineers need to be able to do the following on each cluster node concerning Availability Groups:

  • Remove database from availability group (for restore purposes)
  • Add database to availability group using automatic seeding

We have granted ALTER and CONTROL permissions on the Availability Group to the AD group on both nodes using the statement:

use [master]
GO
GRANT ALTER ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO
use [master]
GO
GRANT CONTROL ON AVAILABILITY GROUP::[BSIBITSQLAG13] TO [BLIJDORP\DL_BSI_BiT_Red_SQL_DB_RW_Admins]
GO

After this the removing part works, the engineers are able to. But adding the database right back using automatic seeding is not working. Using the 'Add Database' wizard the validation step only shows green checkmarks. But when finishing the wizard the following error appears:

error automatic seeding

I tried giving the Availability Group create database permission because of this blog:

USE [master]
GO
ALTER AVAILABILITY GROUP [BSIBITSQLAG13] GRANT CREATE ANY DATABASE;
GO

This script worked, but didn't resolve the error at the other user (I'm sysadmin, they are not). I guess this is where the problem lies, we have to give the AD group rights to give rights to the Availability Group (to execute the above statement). I can't find out which statement to use. Also adding the securityadmins server role to the AD group doesn't work.

Best Answer

The Login must need following permissions to configure availability groups, if the login not already part of sys admin server role:

  1. ALTER ANY AVAILABILITY GROUP
  2. CONTROL AVAILABILITY GROUP
  3. ALTER ANY DATABASE
  4. CREATE ENDPOINT

For detailed information.. and Grant Server Permissions..