Commands / Repair-sqmDbOwnerRisk
sqmSQLTool › Commands › Repair-sqmDbOwnerRisk

Repair-sqmDbOwnerRisk Security

Fixes what Get-sqmDbOwnerRisk finds: removes unexpected db_owner members and replaces the membership with db_datareader, db_datawriter, and a custom db_execute role that is granted EXECUTE on every user stored procedure in the database.

Execution Flow

START dbatools available? throw ERROR process: foreach ($instance in $SqlInstance) Get-DbaDatabase → filter -Database/-ExcludeDatabase/-IncludeSystemDatabases (tempdb always out) foreach ($db in $dbList) Re-query sys.database_role_members for role 'db_owner' - live, not from pipeline exclude MemberName = 'dbo' and anything matching -ExcludeLogin Members found? NO Skip database nothing created/touched YES ShouldProcess? (ConfirmImpact: High) NO WhatIf: record plan per member YES db_execute role missing? → CREATE ROLE [db_execute] AUTHORIZATION [dbo] -ExecuteRoleName overrides the default name 'db_execute' Enumerate sys.procedures (is_ms_shipped = 0) Any procs? NO Skip GRANT step no procedures exist YES GRANT EXECUTE ON OBJECT::[schema].[proc] TO [db_execute] — one statement per procedure foreach ($member in $members) — isolated try/catch each ALTER ROLE db_owner DROP MEMBER [member]; ALTER ROLE db_datareader ADD MEMBER [member]; ALTER ROLE db_datawriter ADD MEMBER [member]; ALTER ROLE [db_execute] ADD MEMBER [member]; catch → Status = Failed, continue Status = OK, Message includes proc count If any repaired: Export-Csv changelog Copy-sqmToCentralPath Return $allResults [List<PSCustomObject>] DONE

Synopsis

Re-detects db_owner membership live per database (it does not trust a possibly stale object piped in from Get-sqmDbOwnerRisk). Databases with nothing to fix are skipped entirely - no role is created, no permission is touched. For databases with unexpected members, the -ExecuteRoleName role (default db_execute) is created once if missing and granted EXECUTE on every user stored procedure; then each offending login is dropped from db_owner and added to db_datareader, db_datawriter, and the execute role. Each login is processed in its own try/catch, so one failing login does not stop the rest.

Requires dbatools. Needs sysadmin or ALTER ANY ROLE + ALTER ANY USER on the target database(s). Full -WhatIf/-Confirm support (ConfirmImpact = 'High') - always dry-run first with -WhatIf before running for real.

Syntax

Repair-sqmDbOwnerRisk
    [-SqlInstance <String[]>]          # pipeline (by property name); default $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]             # pipeline (by property name), alias 'DatabaseName'
    [-ExcludeDatabase <String[]>]
    [-ExcludeLogin <String[]>]
    [-ExecuteRoleName <String>]        # default 'db_execute'
    [-IncludeSystemDatabases]
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMEOne or more SQL Server instances. Pipeline-capable by property name.
-SqlCredentialPSCredential, Optional SQL connection credential.
-DatabaseString[]@() (all)Database name(s) to repair. Wildcards allowed. Accepts DatabaseName from Get-sqmDbOwnerRisk via pipeline.
-ExcludeDatabaseString[]@()Databases to exclude. Wildcards allowed.
-ExcludeLoginString[]@()Principal names to leave alone even if they are db_owner members (wildcards allowed), in addition to the always-excluded dbo.
-ExecuteRoleNameStringdb_executeName of the custom EXECUTE-only role to create/use.
-IncludeSystemDatabasesSwitchfalseAlso process master/model/msdb. tempdb is never processed.
-OutputPathString<module OutputPath>\DbOwnerRiskRepairDirectory for the CSV changelog.
-ContinueOnErrorSwitchfalseContinue to next instance on error instead of throwing.
-EnableExceptionSwitchfalseThrow terminating exceptions immediately.
-WhatIf / -ConfirmSwitch, ShouldProcess (ConfirmImpact: High) guards every role/permission change.

Return Value

PropertyDescription
SqlInstance / DatabaseNameSource instance and database.
LoginNamePrincipal that was removed from db_owner.
ExecuteRoleNameThe execute-only role used (-ExecuteRoleName value).
ProceduresGrantedNumber of stored procedures the execute role was granted EXECUTE on.
StatusOK, Failed, or WhatIf.
MessageResult detail, or the SQL error message on Failed.

Examples

Example 1, Dry run first (always do this before the real thing)

Repair-sqmDbOwnerRisk -SqlInstance "SQL01" -WhatIf

Example 2, Feed straight from the finder

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

Example 3, Scoped to a database pattern, one login left untouched

Repair-sqmDbOwnerRisk -SqlInstance "SQL01" -Database "Prod*" -ExcludeLogin "svc_deploy" -Confirm:$false
⬆ Index
All Commands
🔍 Find It First
Get-sqmDbOwnerRisk