Commands / Sync-sqmLoginsToAlwaysOn
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 / -SourceCredential / -DestinationCredential -AuditAdOrphans? Find-DbaOrphanedFile → report orphans Collect result per secondary Return PSCustomObject[] (one entry per secondary + login) DONE
-SourceCredential and -DestinationCredential are separate credentials for primary and secondary connections, useful in mixed-auth environments. -BackupLogins exports logins to a .sql file before syncing (audit trail). -AuditAdOrphans 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 -SourceCredential).
-SourceCredentialPSCredentialOptionalExplicit credential for the source/primary instance.
-DestinationCredentialPSCredentialOptionalCredential for secondary replica connections.
-AvailabilityGroupNameStringOptionalTarget 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.
-AuditAdOrphansSwitchOptionalAfter sync, report orphaned database users on secondaries.
-EnableExceptionSwitchOptionalThrow terminating errors instead of logging warnings.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.
-AdjustAuthModeswitchOptionalWhen set, automatically adjust target replica authentication mode to match primary if needed.
-BackupPathstringOptionalPath where login backups are stored. Default: configured output path (Get-sqmDefaultOutputPath), i.e. C:\System\WinSrvLog\MSSQL unless overridden in the module config. Path is created if it doesn't exist.
-BackupRetentionDaysintOptionalWhen greater than 0, login backups (LoginBackup_*.sql) in BackupPath older than this many days are deleted after the sync. With -AuditAdOrphans, the LoginAudit_<instance>_* reports are cleaned up too. Default: 7. Set to 0 to disable cleanup (keep all files).
-DisablePolicyswitchOptionalDisable SQL Server policies on secondaries during the copy (default: $true).
-ForceswitchOptionalExisting logins on secondaries are overwritten (password / language / default-db drift), not only new ones added. Default: $true - so a bare 'Sync-sqmLoginsToAlwaysOn' keeps the secondaries fully in sync. Opt out with -Force:$false (then only new logins are created). With SafeForceMode=true (default), all sysadmin logins, the SQL Agent account and system logins (sa via SID, NT SERVICE\*, etc.) are automatically excluded - no self-lockout.
-ForceExcludestring[]OptionalAdditional logins to exclude from Force operation (blacklist). Combined with SafeForceMode exclusions. Default: none.
-ForceIncludeOnlystring[]OptionalWhen Force is set with this parameter, only these logins are updated (whitelist). Overrides other login filters. System logins still excluded per SafeForceMode. Example: 'AppUser_*', 'ServiceAccount'
-IncludeSystemLoginsswitchOptionalWhen set, system logins are also copied. Default: $false.
-NoReportswitchOptionalSee Sync-sqmLoginsToAlwaysOn -? for details.
-RestartServiceIfRequiredswitchOptionalWhen set, restart the SQL Server service on secondary replicas if auth mode was changed.
-SafeForceModeboolOptionalWhen Force is set and SafeForceMode is true (default), automatically excludes dangerous logins: - sa (system admin) - SQL Agent Service Account - NT SERVICE\* (virtual accounts) - BUILTIN\* (Windows built-in accounts) Set to false ONLY if you fully understand the risks. Default: $true
-SkipSecondaryServersstring[]OptionalComma-separated list of secondary instance names to skip (for maintenance). Example: 'SQL02', 'SQL03'

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" -AvailabilityGroupName "AG_PROD"

Example 2, Sync with backup and orphan audit

Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -BackupLogins -AuditAdOrphans

Example 3, Sync specific logins, exclude sa

Sync-sqmLoginsToAlwaysOn -SqlInstance "SQL01" -Login "DOMAIN\AppUser","svc_app" -ExcludeLogin "sa"
⬆ Index
All Commands