sqmSQLTool › Commands › Invoke-sqmLoginAudit

Invoke-sqmLoginAudit Security

Comprehensive audit of SQL Server logins across five categories: BUILTIN\Administrators membership, password policy violations (CHECK_POLICY/CHECK_EXPIRATION/MUST_CHANGE), password age (differentiated by sysadmin status), inactive or never-used logins, duplicate SIDs, and optional AD orphan detection. Outputs a TXT report, findings-only CSV, and optional HTML report. Pipeline-capable for multiple instances.

Execution Flow

START dbatools ($script: dbatoolsAvailable)? throw; return foreach $instance in $SqlInstance Invoke-DbaQuery: sys.server_principals LEFT JOIN sys.sql_logins type IN ('S','U','G') | exclude ##MS_##tokens | LOGINPROPERTY() for PwLastSet/IsMustChange | IS_SRVROLEMEMBER('sysadmin') Filter: -IncludeSystemLogins (NT SERVICE\*, NT AUTHORITY\*) | -ExcludeLogin patterns via _IsExcluded helper 5 audit checks (via _AddFinding helper) Check 0 — BUILTIN\Administrators as sysadmin (if -ReportBuiltInAdmins) Check 1 — Policy: CHECK_POLICY=OFF | CHECK_EXPIRATION=OFF | MUST_CHANGE=ON (sysadmin-differentiated) Check 2 — Password age > MaxPasswordAgeDays (non-sysadmin) or MaxPasswordAgeDaysSysadmin (sysadmin) Check 3 — Inactive (LastLogin > -InactivityThresholdDays) | NeverUsed (SQL login, created > threshold, no last login) Check 4 — Duplicate SIDs among SQL logins (failed migration indicator) Check 5 (optional) — AD orphan: Get-ADObject -Filter {SamAccountName -eq $samName} → not found → AdOrphan Logins with zero findings → FindingType=Clean, Status=OK Requires -CheckAdOrphans switch + ActiveDirectory module (RSAT) ShouldProcess → Write TXT (all findings by category) + CSV (findings only, no Clean) if -GenerateHtmlReport: dark-theme HTML with summary table + per-category sections Copy-sqmToCentralPath | Invoke-sqmOpenReport (-NoOpen skips Notepad) Return List<PSCustomObject> [ SqlInstance, Status, DetailRows[], TxtFile, CsvFile, HtmlFile ] DONE

Synopsis

Each check uses the internal _AddFinding helper to add a structured row to $detailRows. Password-age and policy checks are differentiated between sysadmin and non-sysadmin logins: sysadmins are held to the stricter -MaxPasswordAgeDaysSysadmin threshold (default 365 days) while non-sysadmins use -MaxPasswordAgeDays (default 180). The last-login column is always NULL in sys.server_principals — never-used detection falls back to CreateDate for SQL logins. Duplicate SID detection groups by [System.BitConverter]::ToString($row.Sid).

Requires dbatools (checked via $script:dbatoolsAvailable). AD orphan detection additionally requires the ActiveDirectory module (RSAT). The HTML report is generated inline — no external templates needed.

Syntax

Invoke-sqmLoginAudit
    [-SqlInstance <String[]>]                # default: $env:COMPUTERNAME; pipeline
    [-SqlCredential <PSCredential>]
    [-InactivityThresholdDays <Int>]          # default: 90
    [-MaxPasswordAgeDays <Int>]               # default: 180 (non-sysadmin) | 0 = disable
    [-MaxPasswordAgeDaysSysadmin <Int>]       # default: 365 (sysadmin) | 0 = disable
    [-ExcludeLogin <String[]>]               # wildcards supported
    [-IncludeSystemLogins]                    # include NT SERVICE\*, NT AUTHORITY\*
    [-CheckPolicyNonSysadmin]                 # default: $true
    [-CheckPolicySysadmin]                    # default: $true
    [-ReportBuiltInAdmins]                    # default: $true
    [-CheckAdOrphans]                         # requires ActiveDirectory module
    [-GenerateHtmlReport]                     # default: $true
    [-OutputPath <String>]                   # default: Get-sqmDefaultOutputPath
    [-ContinueOnError] [-EnableException]
    [-NoOpen]                                 # skip auto-open of TXT report
    [-WhatIf]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMEOne or more instances. Pipeline-capable.
-InactivityThresholdDaysInt90Logins without activity since this many days are flagged as inactive.
-MaxPasswordAgeDaysInt180Max password age for non-sysadmin SQL logins. Set to 0 to disable.
-MaxPasswordAgeDaysSysadminInt365Max password age for sysadmin SQL logins. Set to 0 to disable.
-ExcludeLoginString[]—Login names or wildcard patterns to exclude from the audit.
-IncludeSystemLoginsSwitch$falseInclude NT SERVICE\* and NT AUTHORITY\* logins.
-CheckAdOrphansSwitch$falseCheck Windows logins against AD. Requires ActiveDirectory module (RSAT).
-GenerateHtmlReportSwitch$trueGenerate a dark-theme HTML report alongside TXT and CSV.
-OutputPathStringGet-sqmDefaultOutputPathDirectory for report files.
-NoOpenSwitch$falseSkip auto-opening the TXT report after completion.

Return Value

Returns one PSCustomObject per processed instance.

PropertyDescription
SqlInstanceInstance name.
DetailRowsArray of finding objects: LoginName, LoginType, IsEnabled, IsSysadmin, Category, FindingType, Detail, Status.
StatusOK — no findings; Warning — one or more findings; Error — connection error.
TxtFile / CsvFile / HtmlFilePaths to the generated report files.

Examples

# Audit local instance with all defaults
Invoke-sqmLoginAudit

# Audit with AD orphan check
Invoke-sqmLoginAudit -SqlInstance "SQL01" -CheckAdOrphans

# Include system logins, exclude service accounts
Invoke-sqmLoginAudit -SqlInstance "SQL01" -IncludeSystemLogins `
    -ExcludeLogin 'NT SERVICE\*', 'sqmsa'

# Pipeline: audit multiple instances, continue on error
"SQL01", "SQL02" | Invoke-sqmLoginAudit -ContinueOnError

# Check only findings (not clean logins)
$r = Invoke-sqmLoginAudit -SqlInstance "SQL01" -NoOpen
$r[0].DetailRows | Where-Object Status -eq 'Warning' | Format-Table LoginName, Category, FindingType, Detail