Get-sqmDbOwnerRisk Security

Finds databases where non-dbo principals are members of db_owner and reports whether the full privilege-escalation path (TRUSTWORTHY = ON plus a sysadmin-privileged database owner) is actually open. Writes a TXT/CSV/HTML report, colored green for clean databases and red for anything with an unexpected db_owner member.

Execution Flow

Synopsis

db_owner is functionally equivalent to CONTROL on the database: members can create triggers and procedures with EXECUTE AS OWNER, which run in the security context of the database owner (dbo), not the caller. If the database has TRUSTWORTHY set to ON and the database owner maps to a login that holds sysadmin at the server level (common, since databases are usually created by an admin/setup account), any db_owner member can escalate to full instance control via a single CREATE PROCEDURE ... WITH EXECUTE AS OWNER statement.

This function checks every specified database for that condition and classifies it: OK (green) when there are no unexpected db_owner members, Warning (red) when there are but the escalation path is closed, Critical (red) when the full escalation path is open. It only reports — pair it with Repair-sqmDbOwnerRisk to fix what it finds.

Requires dbatools. Background and the full escalation mechanism are written up on the blog: db_owner Risks: Trigger Creation, Ownership Chaining, and the Path to sysadmin.

Syntax

Get-sqmDbOwnerRisk
    [-SqlInstance <String[]>]          # pipeline; default $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-ExcludeDatabase <String[]>]
    [-ExcludeLogin <String[]>]
    [-IncludeSystemDatabases]
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]
    [-NoOpen]

Parameters

Parameter	Type	Default	Description
-SqlInstance	String[]	$env:COMPUTERNAME	One or more SQL Server instances. Accepts pipeline input.
-SqlCredential	PSCredential	,	Optional SQL connection credential.
-Database	String[]	@() (all)	Database name(s) to check. Wildcards allowed.
-ExcludeDatabase	String[]	@()	Databases to exclude. Wildcards allowed.
-ExcludeLogin	String[]	@()	Principal names to exclude from being reported as a risk (wildcards allowed), in addition to the always-excluded `dbo`. Use for deliberately provisioned db_owner accounts.
-IncludeSystemDatabases	Switch	false	Also check master/model/msdb. `tempdb` is never checked.
-OutputPath	String	<module OutputPath>\DbOwnerRisk	Directory to write TXT/CSV/HTML report.
-ContinueOnError	Switch	false	Continue to next instance on error instead of throwing.
-EnableException	Switch	false	Throw terminating exceptions immediately.
-NoOpen	Switch	false	Suppress automatic opening of the generated HTML report.

Return Value

Property	Description
SqlInstance / DatabaseName	Source instance and database.
Status	`OK` (green) or `Risk` (red).
Severity	`OK`, `Warning`, or `Critical`.
RiskIcon	🟢 or 🔴, matching Status.
DbOwnerMembers	Array of unexpected `db_owner` member names (excluding `dbo`/-ExcludeLogin).
MemberCount	Count of `DbOwnerMembers`.
DbOwnerLogin	Login the database owner (`dbo`) maps to.
IsTrustworthyOn	`$true` when the database has `TRUSTWORTHY = ON`.
OwnerIsSysAdmin	`$true` when `DbOwnerLogin` holds `sysadmin`.
EscalationPossible	`$true` when members exist AND TRUSTWORTHY AND owner is sysadmin - the full escalation path.
Message	Human-readable explanation of the finding.

Examples

Example 1, Check all user databases on the local instance

Get-sqmDbOwnerRisk

Example 2, Multiple instances, allow a documented deployment account

Get-sqmDbOwnerRisk -SqlInstance "SQL01","SQL02" -ExcludeLogin "svc_deploy"

Example 3, Just the findings, no report files

Get-sqmDbOwnerRisk -SqlInstance "SQL01" -WhatIf -NoOpen | Where-Object Status -eq 'Risk'

Example 4, Feed findings straight into the repair function

Get-sqmDbOwnerRisk -SqlInstance "SQL01" | Where-Object Status -eq 'Risk' | Repair-sqmDbOwnerRisk -WhatIf

⬆ Index

All Commands

🔧 Fix It

Repair-sqmDbOwnerRisk