sqmSQLTool › Commands › Set-sqmSqlPolicyState

Set-sqmSqlPolicyState Configuration

Enables or disables a single Policy-Based Management (PBM) policy on one or more SQL Server instances using dbatools. Pipeline-capable; operates only on the explicitly named policy without touching the global PBM engine state.

Execution Flow

START dbatools available? throw + ERROR NO Resolve Policy name -Policy param → else: Get-sqmConfig -Key 'DefaultPolicy' Policy name resolved? throw + ERROR NO foreach $instance in $SqlInstance [pipeline-capable] Get-DbaPbmPolicy -Policy $Policy Policy exists? Skip NO ShouldProcess None WhatIf NO Resolve SMO policy object Try: policyObject.Policy.Enabled Fallback: policyObject.Enabled (v2) null → throw smoPolicy.Enabled = $targetEnabled; .Alter() catch: add Failed result; if EnableException → throw; if !ContinueOnError → throw Return $results[] DONE

Synopsis

Uses dbatools Get-DbaPbmPolicy to locate the named policy on each target instance, then sets smoPolicy.Enabled and calls .Alter() on the SMO object. The function handles both dbatools wrapper objects (with a .Policy sub-property, older versions) and direct SMO objects (dbatools 2.x). Only the explicitly named policy is changed — the global PBM engine state is not modified.

If no -Policy parameter is supplied, the policy name is read from the module configuration key DefaultPolicy (set via Set-sqmConfig -DefaultPolicy 'PolicyName').

Syntax

Set-sqmSqlPolicyState
    [-SqlInstance <String[]>]       # pipeline-capable
    [-SqlCredential <PSCredential>]
    [-Policy <String>]
    -State <Enable|Disable>         # required
    [-ContinueOnError]
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMEOne or more SQL Server instances. Accepts pipeline input.
-SqlCredentialPSCredential—Credential for the SQL connection.
-PolicyStringConfig DefaultPolicyExact name of the PBM policy to toggle. Falls back to DefaultPolicy config key if not supplied.
-StateStringRequiredEnable or Disable the policy.
-ContinueOnErrorSwitchfalseContinue processing remaining instances on error.
-EnableExceptionSwitchfalseThrow terminating exceptions (overrides ContinueOnError).

Return Value

Array of [PSCustomObject] — one entry per instance:

PropertyDescription
SqlInstanceTarget SQL Server instance.
PolicyPolicy name that was toggled.
StateRequested state: Enable or Disable.
StatusSuccess / Skipped / WhatIfSkipped / Failed.
MessageHuman-readable result or error description.

Examples

# Disable a specific policy on one instance
Set-sqmSqlPolicyState -SqlInstance "SQL01" -Policy "xp_cmdshell must be disabled" -State Disable

# Enable a policy on multiple instances via pipeline
"SQL01","SQL02" | Set-sqmSqlPolicyState -Policy "Password Policy" -State Enable

# Use DefaultPolicy from module config, preview with -WhatIf
Set-sqmSqlPolicyState -SqlInstance "SQL01" -State Enable -WhatIf

# Process a list with ContinueOnError so all instances are attempted
Set-sqmSqlPolicyState -SqlInstance (Get-Content "servers.txt") `
    -Policy "Surface Area" -State Disable -ContinueOnError

Notes

The SMO resolution handles two dbatools return formats: older versions wrap the SMO object under a .Policy property; dbatools 2.x returns the SMO object directly. Both are detected automatically. If neither provides an Enabled property, a descriptive exception is thrown listing the available properties.
Policy-Based Management operates at the SQL Server engine level. Enabling or disabling a policy with On Change: Prevent execution mode may immediately block T-SQL statements if the policy condition is violated.