Register-sqmAuditSession

SecuritysqmSQLTool v1.9.115+ · Register🔓 ALTER ANY EVENT SESSION

Creates (if missing) and starts a dedicated Extended Events session covering the login/database/metadata "audit problem": -FailedLogins (error_reported filtered to 18456), -SuccessfulLogins (login), -DatabaseCreated/-DatabaseDropped (database_created/database_dropped, always instance-wide), and -MetadataChanges (object_created/object_altered/object_deleted, filtered to the commit phase, optionally scoped to specific databases with -TargetDatabase). Every category is independently selectable, or use -All. No dedicated reader is needed: every event collects the same standard actions Invoke-sqmExtendedEvents -Read already surfaces.

Parameters

Parameter	Type	Required	Default	Notes
-SqlInstance	string	Optional	$env:COMPUTERNAME	Target SQL Server instance.
-SqlCredential	PSCredential	Optional	,	SQL or Windows credential.
-SessionName	string	Optional	sqm_AuditSession	Name of the Extended Events session.
-FailedLogins	switch	Switch	$false	Capture failed login attempts (error 18456).
-SuccessfulLogins	switch	Switch	$false	Capture successful logins.
-DatabaseCreated	switch	Switch	$false	Capture CREATE DATABASE. Always instance-wide, see below.
-DatabaseDropped	switch	Switch	$false	Capture DROP DATABASE. Always instance-wide, see below.
-MetadataChanges	switch	Switch	$false	Capture schema-level DDL (object created/altered/deleted).
-All	switch	Switch	$false	Shorthand for every category above.
-TargetDatabase	string[]	Optional	,	Scopes `-MetadataChanges` to these databases. Has no effect on `-DatabaseCreated`/`-DatabaseDropped` (verified live: no working predicate exists for those two events).
-IncludeSystemGeneratedObjects	switch	Switch	$false	Also capture temp objects (`#...`) and auto-generated statistics (`_WA_Sys_...`) under -MetadataChanges. Excluded by default as noise.
-IncludeFileTarget	switch	Switch	$false	Adds a durable `package0.event_file` target alongside the ring buffer. Only applied when the session is first created.
-FileTargetPath	string	Optional	instance error log dir	Directory for the event_file target, only used with -IncludeFileTarget.
-EnableException	switch	Switch	$false	Throw exceptions immediately instead of returning as errors.

Execution Flow

Verified Live, Not Assumed

database_created/database_dropped exist as real, dedicated Extended Events (a related blog post originally claimed otherwise, corrected after this function was tested).
object_created/object_altered/object_deleted fire twice per statement (start and commit/rollback) — filtered to ddl_phase = 1 so nothing is double-counted.
Scoping -DatabaseCreated/-DatabaseDropped by database name does not work: neither the collected database_name action (reflects the connecting session's existing context, not the database being created/dropped) nor a direct reference to the event's own field produces a working filter. Both were tried live and both silently matched zero rows.
-TargetDatabase scoping for -MetadataChanges was verified working: an out-of-scope table's DDL was confirmed excluded when scoped to a different database.
Temp objects and auto-generated statistics objects showed up as real noise in live testing (including from dbatools' own connection-housekeeping temp tables) — excluded by default under -MetadataChanges.

Examples

Register-sqmAuditSession -SqlInstance "SQL01" -FailedLogins -SuccessfulLogins

Every category, with durable file-based retention

Register-sqmAuditSession -SqlInstance "SQL01" -All -IncludeFileTarget

Schema-change auditing scoped to two databases, nothing else

Register-sqmAuditSession -SqlInstance "SQL01" -MetadataChanges -TargetDatabase "Orders", "Billing"

Set up, then read back with the module's existing Extended Events reader

Register-sqmAuditSession -SqlInstance "SQL01" -All
Invoke-sqmExtendedEvents -SqlInstance "SQL01" -SessionName sqm_AuditSession -Read

Invoke-sqmExtendedEvents