1

We are having the following setting: For an ERP Test / Accept SQL Cluster we want to follow the least privilige 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 recondary on true. The engineers are added to an AD group. We grant permissions to that AD group.

The enigneers 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: https://chrislumnah.com/2017/05/16/automatic-seeding-error/

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). But i can't find out which statement to use. Also adding the securityadmins server role to the AD group doesn't work.

I you have any clue or idea about this, please reply. Any help is much appreciated.

Kind regards,

Jogchum Rooda

3

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

  • Hi Shekar, Thanks for this comment, it worked! I didn't grant the CREATE ENDPOINT, since the external engineers only have to remove and add the database (after a database restore). Granting the ALTER ANY DATABASE (with grant option), fixed my problem. Thank you! – Jogchum Rooda Aug 22 at 11:50
  • Good to hear that! – Shekar Kola Aug 22 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.