There are lot of SQL Server Instances configured with Alwayson Availability Groups HADR in our environment.
When we create a login on Primary Replica of an Availability Group it will not be automatically moved or get sync with all secondary replicas
This will cause an issue(Failed login error messages) when a Failover occurs as in current Primary replica the login does not exist .
To avoid such issues I have googled for script and found one PowerShell script which suits ours requirement ( I have automated this long back ago , didn’t remember the site or link to provide here – thanks to the original Author who created this script ) we automated by creating a copy logins job on all replicas, here I am using a PowerShell script to perform our task
Creating an SQL Agent job which executes PowerShell script on all replicas and this job is smart enough to identify on which node it should execute.
On Primary replica When a new login creation or password update or changing\providing permissions occurs, as per the schedule when this job triggered the script will replicate all the changes or modifications done on Primary replica to all the secondary replicas.
Need to have stored procedure ‘Sp_hexadecimal’ Script on all replicas.
This script will not do anything until there are any modifications done on Primary replica.
This script does not copy existing logins until you make any changes to them e.g. password change, grant/revoke privileges etc.
Schedule this job on all replica based on your environment and need.
Before implementing in Production environment please do testing
Created new SQL Agent job Copy\Sync-Logins and added a Job Step Sync as shown below :
In Job Step given Step Name as ‘ Sync Logins’ and Type as ‘ Operating System (CmdExec) ‘ and command as ‘powershell -file “C:\temp\Sync-Logins.ps1”
click OK–>Schedule job–>OK–>OK, then the job gets created.
After creating the Job on Primary replica Script out and execute it on all secondary replicas.
Whenever there is a modification done on Primary replicas those will get replicated to all secondary when the scheduled job runs.
Please find the Full Script in below link: