Sync logins and Agent jobs across all replicas in Distributed Availability Group

availability-groupsdistributed-availability-groupshadr

I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on..

Is there any way to sync all logins and permissions from global primary to forwarder and other replicas?

Also, how to sync SQL agent jobs using any automated way?

Best Answer

For Database level permissions:
Maintain SID of the Login same across all replicas (SQL Instances). I think, you don't have to even maintain Login sync jo. Instead, create login using Registered Servers using following steps, as long as the SID remain same, the database permissions assigned in Primary replica would automatically synchronized into all secondary (including forwarder) replicas.

Step1: Create login in DEV SQL Instance
Step2: Get the SID of Login (from DEV)
Step3: Create login in Production Instances using Registered Servers

Create Login MyLogin with password = 'xxxx', SID=0x000000000000;

Step4: Assign database level permissions for the login (Only in primary replica)

For SQL Agent Jobs:
You can use anyone of approach:

  • Script-out and execute via Registered servers.
  • If it feasible in your environment, you can configure multi-server administration feature.
Related Question