Syncing SQL Logins Across an AlwaysOn Availability Group

Q: Can login sync for an AlwaysOn group run on a schedule instead of manually?

Yes. New-sqmAutoLoginSyncJob creates a SQL Server Agent job on the primary replica that runs the sync automatically, so it's a standing job instead of a task someone has to remember after every new login or password change.

Why this is a standing problem, not a one-time setup step

An Availability Group replicates the databases in it. Logins live at the server (instance) level, outside any database, so they are never part of AG data movement, contained databases aside. The moment someone creates a login on the current primary, every secondary is out of sync until someone copies it across, matching the SID exactly so it maps back to the database user after failover.

This isn't a one-time migration task. It's recurring: every new application login, every password rotation for a SQL login, every new service account, has to make it to every replica, indefinitely, for as long as the AG exists.

What Copy-DbaLogin actually covers

dbatools' Copy-DbaLogin is the standard tool for this, and it's genuinely solid at what it does: it copies logins between two instances with matching SIDs, so Windows and SQL logins both map correctly on the other side.

Copy-DbaLogin -Source "Node1" -Destination "Node2" -Login "AppSvc", "ReportUser"

What it doesn't do on its own:

Detect which logins exist on the primary but are missing on a given secondary, you supply the list
Run on a schedule, it's a manual, one-off invocation
Know which replica is currently primary, you point it at a specific source and destination yourself
Distinguish "safe to overwrite" accounts from sa or the SQL Server Agent service account when you do use -Force for a password change

The password-rotation trap A SQL login's password change doesn't propagate through AlwaysOn either. Re-running a login copy with -Force to push the new password is correct for the login that changed, and dangerous if it silently overwrites system accounts you didn't mean to touch.

What Sync-sqmLoginsToAlwaysOn and New-sqmAutoLoginSyncJob add

AG-aware by default. It detects the current primary replica and syncs outward to secondaries, instead of you tracking which node is which after every failover.
-SafeForceMode. Auto-excludes sa, the SQL Agent service account, and other system logins from -Force overwrites, so a routine password-sync run can't clobber an account that keeps the instance itself running.
-ForceIncludeOnly. A whitelist for the opposite case: update exactly these logins' passwords, nothing else, when you do need to push a specific rotation.
-BackupLogins. Generates a rollback script before applying changes.
New-sqmAutoLoginSyncJob. Creates a SQL Server Agent job on the primary that runs the sync automatically (daily at 2:00 AM by default), so login drift stops being something a human has to remember.

# One-time setup: schedule daily sync on the AG
New-sqmAutoLoginSyncJob -SqlInstance "SQL01" -AvailabilityGroupName "ProdAG"

# Ad-hoc run right now, with a rollback script first
Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -AvailabilityGroupName "ProdAG" `
    -Force -SafeForceMode -BackupLogins

Full command reference Sync-sqmLoginsToAlwaysOn, New-sqmAutoLoginSyncJob, and the rest of sqmSQLTool's AlwaysOn functions are documented in the command reference.

Questions people actually ask

Q: Does AlwaysOn replicate SQL Server logins automatically? No. Availability Groups replicate the databases you add to them. Server-level logins exist outside any database and are never part of AG data movement, so every replica needs its own copy of every login, kept in sync manually or by a separate process.

Q: Is Copy-DbaLogin enough to keep AlwaysOn replicas in sync? For a one-time copy, yes, it's the right tool. For ongoing sync as new logins get created and passwords rotate after go-live, it needs to be wrapped in something that runs on a schedule, targets the current primary automatically, and won't blindly overwrite system accounts on a routine password-sync run.

Q: What happens if a login's SID doesn't match between the primary and a secondary? The database user on the secondary can't map back to the server login after failover. Applications connecting with that login get "login failed" errors even though the login exists by name, because SQL Server resolves database permissions through the SID, not the login name.

Q: Can login sync for an AlwaysOn group run on a schedule instead of manually? Yes. New-sqmAutoLoginSyncJob creates a SQL Server Agent job on the primary replica that runs the sync automatically, so it's a standing job instead of a task someone has to remember after every new login or password change.

← Back to Blog