Always On

Sync-sqmLoginsToAlwaysOn

Copies SQL Server logins from the primary replica to all secondaries of an Availability Group using Copy-DbaLogin — with optional SID-preserving backup/restore, orphan detection, and exclude filters.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (Medium)
Output: PSCustomObject[]

Execution Flow

START 1. Get-DbaAvailabilityGroup → resolve primary + secondaries AG / primary found? throw error -BackupLogins? Export-DbaLogin → backup .sql foreach secondary replica ShouldProcess: sync logins to secondary? skip (WhatIf) Copy-DbaLogin -Source primary -Destination secondary -Login / -ExcludeLogin / -SourceSqlCredential / -DestinationSqlCredential -AuditOrphans? Find-DbaOrphanedFile → report orphans Collect result per secondary Return PSCustomObject[] (one entry per secondary + login) DONE
-SourceSqlCredential and -DestinationSqlCredential are separate credentials for primary and secondary connections — useful in mixed-auth environments. -BackupLogins exports logins to a .sql file before syncing (audit trail). -AuditOrphans reports database users on the secondary that have no matching server login after the sync.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalPrimary replica instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL credential for the primary (alias for -SourceSqlCredential).
-SourceSqlCredentialPSCredentialOptionalExplicit credential for the source/primary instance.
-DestinationSqlCredentialPSCredentialOptionalCredential for secondary replica connections.
-AvailabilityGroupStringOptionalTarget AG name. If omitted, all AGs on the instance are processed.
-LoginString[]OptionalSync only these specific login names.
-ExcludeLoginString[]OptionalLogin names to skip during sync.
-BackupLoginsSwitchOptionalExport logins to a .sql backup file before syncing.
-AuditOrphansSwitchOptionalAfter sync, report orphaned database users on secondaries.
-EnableExceptionSwitchOptionalThrow terminating errors instead of logging warnings.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject[] — one entry per login per secondary — with: Primary, Secondary, Login, Status, Message.

Examples

Example 1 — Sync all logins to all AG secondaries

Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -AvailabilityGroup "AG_PROD"

Example 2 — Sync with backup and orphan audit

Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -BackupLogins -AuditOrphans

Example 3 — Sync specific logins, exclude sa

Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -Login "DOMAIN\AppUser","svc_app" -ExcludeLogin "sa"