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?


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.

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.