Commands / Compare-sqmAlwaysOnRoles
AlwaysOn

Compare-sqmAlwaysOnRoles

Compares server-role membership (sysadmin, dbcreator, custom roles, ...) for every login across all replicas of an Availability Group. AlwaysOn only replicates databases — master and its server-role assignments are not synchronized, so a login can silently lose (or gain) sysadmin after a failover. Diagnostic sibling of Compare-sqmAlwaysOnLogins, which checks login existence/attributes rather than role membership.

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

Execution Flow

START Resolve -AvailabilityGroupName (or first AG found, warn if >1) List replicas + roles (Primary/Secondary) from sys.availability_replicas foreach replica: query sys.server_role_members (modern query, legacy fallback pre-2022) unreachable → excluded, not failed Build login union across reachable replicas only foreach login: filter sa / system / -Login / -ExcludeLogin, then compare Missing on any replica, or sysadmin membership differs? Critical Present on all, but other role sets differ? Warning OK -OnlyDifferences && status == OK? skip row next login → ShouldProcess && -not -NoReport? Write TXT + HTML report, open unless -NoOpen -FailOnDrift && Warning/Critical > 0? Write EventId 9010 then throw Return PSCustomObject[] (per login)
-FailOnDrift is built for unattended SQL Agent job use: a job step that only calls Compare-sqmAlwaysOnRoles -FailOnDrift writes the report as usual but then throws if any drift was found, turning a red job step into an OnFailure-operator alert. The report is always written first, even when the function subsequently throws. Database roles (db_owner, etc.) are deliberately out of scope — they live inside the replicated database itself and rarely diverge; orphaned SIDs there are handled by Repair-sqmAlwaysOnDatabases instead.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalEntry-point instance of the AG (primary or a secondary). Default: local computer name.
-AvailabilityGroupNameStringOptionalAG to compare. Without it, the first AG found is used (warns if there's more than one).
-SqlCredentialPSCredentialOptionalPSCredential used for all replicas.
-IncludeSystemLoginsSwitchOptionalAlso compare system logins (sa, ##MS_*, NT SERVICE\*, NT AUTHORITY\*, BUILTIN\*). Hidden by default.
-LoginString[]OptionalOnly compare these logins (wildcards allowed).
-ExcludeLoginString[]OptionalExclude these logins (wildcards allowed).
-OnlyDifferencesSwitchOptionalOnly output logins with a Warning or Critical status.
-OutputPathStringOptionalOutput directory for TXT/HTML. Default: from module configuration.
-NoOpenSwitchOptionalSuppress automatically opening the HTML report.
-FailOnDriftSwitchOptionalOn drift (Warning/Critical), write a Windows Event (Source sqmSQLTool, EventId 9010) and throw — fails a calling SQL Agent job step. Implies -NoOpen; the report is still written first.
-ContinueOnErrorSwitchOptionalContinue on error instead of aborting.
-EnableExceptionSwitchOptionalThrow errors immediately as exceptions.

Return Value

Returns a PSCustomObject[] — one entry per compared login — with: LoginName, LoginType, OverallStatus (OK, Warning, or Critical), Present (e.g. "2/2"), MissingOn, RolesConsistent (bool), Roles, IsSysadminDrift (bool), Details (per-replica breakdown). Also writes TXT and HTML reports.

Examples

Example 1 — Compare roles for the first AG found

Compare-sqmAlwaysOnRoles -SqlInstance "SQL01"

Example 2 — Show only logins with drift, for a named AG

Compare-sqmAlwaysOnRoles -SqlInstance "SQL01" -AvailabilityGroupName "AG_Prod" -OnlyDifferences

Example 3 — Unattended SQL Agent job step (fails the job on drift)

Compare-sqmAlwaysOnRoles -SqlInstance "SQL01" -FailOnDrift

Example 4 — Pipe results into a table

Compare-sqmAlwaysOnRoles -SqlInstance "SQL01" | Format-Table
⬆ Index
All Commands