One option to automate sync'n the logins between your AG replicas, if desired, but can be used as a one time thing as well.
dbatools is a module that offers some code for migration of SQL Server instances, but I have used to also keep AG replicas in sync.
The main function you need to simply sync logins is Copy-SqlLogin
. This will include the SID, password and other properties as seen via SSMS (database mappings, etc.). It is similar functionality that the script Tara referenced.
Copy-SqlLogin -Source DB1 -Destination DB2 -SyncOnly
That command alone will copy the logins from DB1 over to DB2 and simply sync up anything that changed. So say you add another database to the AG and grant a login access (map a user), then next time you run the above command it will update that login on DB2 for you.
The below script is included for completeness just to show what can be done. I built the below process on a client's 4 node AG as a Scheduled Task. As well this offers the ability to exclude replicas if you need; it was something they required.
#+++For debug just remove the "ErrorAction" and "WarningAction" parameters from the command+++
$localServerName = 'DB1'
$AGName = 'AG1'
$replicaExclude = 'DB3'
$excludeJobList = 'syspolicy_purge_history','DatabaseBackup - SYSTEM_DATABASES - FULL','DatabaseBackup - USER_DATABASES - DIFF','DatabaseBackup - USER_DATABASES - FULL','DatabaseBackup - USER_DATABASES - LOG'
$logfile = 'C:\Scripts\SyncProcess.log'
# Where I had stored the module on the client computer
Import-Module 'C:\Scripts\dbatools\dbatools.psm1'
if (Test-Path $logfile) {Remove-Item $logfile -Force}
Start-Transcript -Path $logfile -Force -NoClobber
$r = Get-SqlAvailabilityGroup -SqlServer $localServerName -AvailabilityGroupname $AGName -IsPrimary
if ($r)
{
$srvs = (Get-SqlAvailabilityGroup -SqlServer $localServerName -AvailabilityGroupname $AGName | where {$_.ReplicaName -ne $localServerName -and $replicaExclude -NotContains $_.ReplicaName}).ReplicaName
foreach ($s in $srvs)
{
Write-Host "Working on $s" -ForegroundColor Red
#Copy logins to destination, ignoring errors and warning messages to keep output low
Copy-SqlLogin -Source $localServerName -Destination $s -SyncOnly -ErrorAction SilentlyContinue -WarningAction SilentlyContinue
#copy operators
Copy-SqlOperator -Source $localServerName -Destination $s
#Copy jobs to destination
Copy-SqlJob -Source $localServerName -Destination $s -Force -Exclude $excludeJobList
}
}
Stop-Transcript