sqmSQLTool › Commands › Get-sqmSysadminAccounts

Get-sqmSysadminAccounts Security

Lists all SQL Server logins that are direct members of the sysadmin server role. Classifies each login as SA, BuiltinAdmins, Excluded, Disabled, or Unexpected. Supports pattern-based exclusion and automatic system-account filtering. Outputs TXT and CSV reports.

Execution Flow

START dbatools available? throw ERROR if -ExcludeSysAccounts → add NT SERVICE\*, NT AUTHORITY\*, ##MS_*## foreach $instance in $SqlInstance [pipeline] Invoke-DbaQuery: sys.server_principals JOIN sys.server_role_members WHERE sr.name='sysadmin' AND sp.type IN (S,U,G,R) AND principal_id > 1 foreach $row — classify each login SA: SID = 0x01 BuiltinAdmins: name = 'BUILTIN\Administrators' → SECURITY REVIEW Excluded: _IsExcluded() → -like pattern match Disabled: is_disabled=1 | Unexpected: everything else ShouldProcess → Write TXT (sections) + CSV Status: Warning if Unexpected or BuiltinAdmins > 0 else OK Return $allInstanceResults[] DONE

Synopsis

Queries sys.server_principals JOIN sys.server_role_members for all direct sysadmin members. Each login is classified into one of five statuses. BUILTIN\Administrators always gets its own BuiltinAdmins status and is never silently excluded. The TXT report contains four sections: BuiltinAdmins, Unexpected, Disabled, SA, and Excluded accounts.

Requires dbatools. Compatible with SQL Server 2008+ (password_last_set_time and last_login_date are intentionally excluded for older version compatibility). Use -ExcludeSysAccounts to suppress expected service accounts from the Unexpected section.

Login Status Values

StatusMeaning
BuiltinAdminsBUILTIN\Administrators — security review required.
UnexpectedNo exclusion defined — requires manual review.
DisabledHas sysadmin rights but login is disabled.
ExcludedMatched by -ExcludeLogin pattern or -ExcludeSysAccounts.
SAThe SA account (SID 0x01).

Syntax

Get-sqmSysadminAccounts
    [-SqlInstance <String[]>]     # pipeline
    [-SqlCredential <PSCredential>]
    [-ExcludeLogin <String[]>]     # wildcards allowed
    [-ExcludeSysAccounts]           # adds NT SERVICE\*, NT AUTHORITY\*, ##MS_*##
    [-IncludeDisabled <Bool>]      # default $true
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMESQL Server instance(s). Pipeline-capable.
-SqlCredentialPSCredential—SQL connection credential.
-ExcludeLoginString[]@()Login names or wildcard patterns to mark as Excluded.
-ExcludeSysAccountsSwitchfalseAuto-exclude NT SERVICE\*, NT AUTHORITY\*, ##MS_*## patterns.
-IncludeDisabledBool$trueInclude disabled sysadmin logins in the report.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for TXT and CSV report files.
-ContinueOnErrorSwitchfalseContinue on error for an instance.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Examples

# Basic audit — local instance
Get-sqmSysadminAccounts

# Exclude known service accounts automatically
Get-sqmSysadminAccounts -SqlInstance "SQL01" -ExcludeSysAccounts

# Exclude specific accounts by name pattern
Get-sqmSysadminAccounts -SqlInstance "SQL01" -ExcludeLogin "DOMAIN\svc_sql","DOMAIN\dba_*"