Commands / Get-sqmLoginLastAccess
Get-sqmLoginLastAccess
Security & Audit sqmSQLTool v1.9.x · Get
SQL Server does not persist a "last login" timestamp anywhere. This function collects what the instance can actually prove and reports the source and the confidence for every value, instead of guessing.
Two sources, newest value wins: Live (sys.dm_exec_sessions, only sessions that exist right now, wiped by every service restart, can never look further back than sqlserver_start_time) and ErrorLog (successful login messages in the SQL Server error log, only available when the AuditLevel registry value is 1 or 3, the default of 2 records nothing usable here, and limited by log rotation).
LastAccess = $null means "not provable", NOT "never used". That distinction is deliberately not collapsed — a login can be actively used entirely outside both sources' coverage window.
The error log parser is language-neutral: message templates for events 18453 / 18454 are read from sys.messages and turned into regular expressions at runtime, since wording and even quoting differ per SQL Server language (English: user 'x', German: Benutzer "x").

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestring[]Optional$env:COMPUTERNAMEOne or more instances. Pipeline-capable.
-SqlCredentialPSCredentialOptional, SQL login credentials.
-Loginstring[]Optional@() (all)Filter: only these logins. Wildcards allowed.
-ExcludeSystemLoginsswitchSwitch$falseHide NT SERVICE\*, NT AUTHORITY\* (incl. localized variants) and ##MS_*##.
-SourcestringOptionalAllWhich sources to query: All, Live, ErrorLog.
-OutputPathstringOptional, If given, CSV and HTML reports are written and copied to the central path.
-ContinueOnErrorswitchSwitch$falseContinue with the next instance on error instead of aborting the whole run.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately.
-NoOpenswitchSwitch$falseDo not automatically open the generated report after creation.

Execution Flow

START dbatools available? NO throw: Install dbatools YES foreach $instance in -SqlInstance (pipeline-capable) Query sys.server_principals (type S/U/G) → login list Apply -ExcludeSystemLogins and -Login filters No logins left? → warn, continue to next instance Read sqlserver_start_time + GETDATE() (CoverageSince, ServerNow) -Source in All / Live? YES sys.dm_exec_sessions GROUP BY login_name MAX(login_time, last_request_end_time) → $liveMap, session count → $sessionMap NO -Source in All / ErrorLog? YES xp_instance_regread AuditLevel (unreadable → warn, treat as 0) AuditLevel in 1 / 3? NO only failed logins logged YES Build language-neutral regex from sys.messages (18453 / 18454 templates) Get-DbaErrorLog → match each line → newest hit per login → $logMap No usable message template found → warn, ErrorLog source skipped for this instance foreach login: merge $liveMap + $logMap, newest wins both present → Confidence=Exact, later of the two, Source=Live or ErrorLog one present → Confidence=Exact, that source neither → LastAccess=$null, Source=None, Confidence=Unknown No value: WINDOWS_GROUP → explanatory Note (members log in under their own account) else: Note explains WHY there's no proof - not evidence of non-use Add [PSCustomObject] to $allResults → next instance -OutputPath given? NO YES Export-Csv + ConvertTo-sqmHtmlReport Copy-sqmToCentralPath Open HTML unless -NoOpen Return $allResults DONE

Output object

FieldContents
SqlInstance / LoginName / LoginTypeWhich login, on which instance, and its type (SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP).
IsDisabled / CreateDateFrom sys.server_principals.
LastAccessNewest provable timestamp, or $null if nothing could be proven.
DaysSinceDays between LastAccess and the server's own current time. $null if LastAccess is $null.
SourceLive, ErrorLog, or None.
ConfidenceExact (a source proved a value) or Unknown (nothing provable — not "never used").
ActiveSessionsCurrent session count for this login from the Live source, 0 if none.
CoverageSinceInstance's sqlserver_start_time — the Live source cannot see further back than this.
ErrorLogUsableWas the ErrorLog source actually usable for this instance (AuditLevel 1/3 and a resolvable message template)?
NoteExplains an empty result, or flags a Windows group login.

Examples

Check one instance, all sources
Get-sqmLoginLastAccess -SqlInstance "SQL01"
Live sessions only, hide system logins
Get-sqmLoginLastAccess -SqlInstance "SQL01" -Source Live -ExcludeSystemLogins
Find logins with no provable access at all
Get-sqmLoginLastAccess -SqlInstance "SQL01" | Where-Object Confidence -eq 'Unknown'
Multiple instances via pipeline, CSV + HTML evidence
"SQL01", "SQL02" | Get-sqmLoginLastAccess -OutputPath "D:\Reports\LoginAudit" -NoOpen

Requirements

Requires dbatools. Needs VIEW SERVER STATE for the Live source; the ErrorLog source additionally needs securityadmin or sysadmin (xp_instance_regread / xp_readerrorlog). Windows GROUP logins usually show no session of their own — a member connects with their own AD account, so sys.dm_exec_sessions reports the account, not the group. Such logins carry an explanatory Note.

See also

Get-sqmLoginPermissions, what a login can actually do.
Get-sqmSysadminAccounts, who currently holds sysadmin.

⬆ Index
All Commands