Commands / Compare-sqmAlwaysOnLogins
Compare-sqmAlwaysOnLogins
Always On sqmSQLTool v1.8.2+ · Compare ✓ -WhatIf supported
Compares SQL Server logins across all replicas of an Always On Availability Group and reports drift. Per login the function checks: presence on every replica, SID consistency, password-hash equality (SQL logins only), default database, and default language. Each login receives a status of Critical (missing on a replica, SID mismatch, or password-hash drift), Warning (default DB or language differs), or OK.

Output is returned as a [PSCustomObject] collection and written as a TXT + HTML report (auto-opened unless -NoOpen). Use -FailOnDrift to let a SQL Agent job step fail automatically when drift is detected — the function then writes Windows Event 9001 and throws an exception.

The sa account (identified by SID 0x01, regardless of rename) is excluded by default because each instance intentionally uses its own random password that is never synchronized. Diagnostic counterpart to Sync-sqmLoginsToAlwaysOn.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEEntry point — Primary or any Secondary of the AG.
-AvailabilityGroupNamestringOptional— (first AG found)Name of the AG to compare. If omitted, the first AG found is used; a warning is issued when multiple AGs exist.
-SqlCredentialPSCredentialOptional—Used for all replica connections.
-IncludeSystemLoginsswitchSwitch$falseAlso compare sa, ##MS_*, NT SERVICE\*, NT AUTHORITY\*, BUILTIN\*. When set, sa password-hash drift is reported (usually Critical).
-Loginstring[]Optional@() = allInclude only these logins. Wildcards allowed.
-ExcludeLoginstring[]Optional@()Exclude these logins. Wildcards allowed.
-OnlyDifferencesswitchSwitch$falseReturn and report only logins with status Warning or Critical.
-OutputPathstringOptionalGet-sqmDefaultOutputPathTarget directory for TXT and HTML report files.
-NoOpenswitchSwitch$falseSuppress automatic opening of the HTML report.
-FailOnDriftswitchSwitch$falseWrite Windows Event 9001 (Source: sqmSQLTool) and throw when any Warning or Critical logins exist. Designed for SQL Agent job steps. Implies -NoOpen.
-ContinueOnErrorswitchSwitch$falseLog errors and continue instead of re-throwing.
-EnableExceptionswitchSwitch$falseThrow immediately on error (dbatools-style).
-WhatIf / -ConfirmswitchOptional—ShouldProcess guard on the report-writing step. Skips TXT/HTML output in -WhatIf mode.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Lookup 'sa' name via SID 0x01 SUSER_SNAME(0x01) — try/catch → fallback name 'sa' AGs found on SqlInstance? NO no AGs found warn / return YES Resolve AvailabilityGroupName provided → validate exists | not provided → first AG (warn if multiple) foreach replica in $replicaNames Query sys.server_principals + sys.sql_logins build login map per replica | unreachable → $null + WARNING No replicas reachable? YES ERROR: no replica reachable / return NO Build login name union collect all login names across reachable replicas (lowercase key) foreach login in union (sorted) Filter: skip system/sa (unless -IncludeSystemLogins) Filter: -Login (include wildcards) | -ExcludeLogin (exclude wildcards) Collect per replica: DefaultDatabase, Language, SidHex, PwdHash Status: Critical (missing / SID mismatch / hash mismatch) | Warning (DB/lang) | OK -OnlyDifferences: skip OK | add [PSCustomObject] to $results ShouldProcess & !NoReport? NO -WhatIf / -NoReport skip report generation YES Write TXT + HTML report to OutputPath AlwaysOnLoginCompare_<AG>_<date>.txt / .html Invoke-sqmOpenReport auto-open HTML; skip if -NoOpen or -FailOnDrift (implies -NoOpen) FailOnDrift & drift found? YES Write EventLog 9001 throw drift error NO Return $results [PSCustomObject[]] — LoginName, OverallStatus, Present, MissingOn, Details … DONE

Examples

Compare all logins on the first AG found on SQL01
Compare-sqmAlwaysOnLogins -SqlInstance "SQL01"
Show only differences for a specific AG
Compare-sqmAlwaysOnLogins -SqlInstance "SQL01" -AvailabilityGroupName "AG_Prod" -OnlyDifferences
Pipe to Format-Table for a quick console overview
Compare-sqmAlwaysOnLogins -SqlInstance "SQL01" | Format-Table
SQL Agent job step — fail the step when login drift is detected
Compare-sqmAlwaysOnLogins -SqlInstance "SQL01" -AvailabilityGroupName "AG_Prod" -FailOnDrift -OnlyDifferences
← Back
Command Index
Related
Add-sqmDatabaseToAG