How to enable, disable and delete a policy created in policy management

Problem: How to enable, disable and delete a policy created in policy management?

Solution:

Disable and Enable a policy

After a while using a policy, you may feel that a policy is old and disable it for creating a new policy.

For disabling a policy category go using management studio, please see the below picture to locate where to go and what options should be selected.

There is no way for us to disable or enable a policy or policy condition using T-SQL, you can only do a drop or create using T-SQL.

Deleting a policy

For deleting a policy using T-SQL there are system stored procedures which can have an optional parameter to send in a policy name or policy id but not both.

Syntax:

•  Deletes a policy: sp_syspolicy_delete_policy @name,@id 
• Deletes a policy condition: sp_syspolicy_delete_condition @name,@id

When you are planning to delete a policy condition you must delete a policy which is referencing it as the object has references in the policy.

Or else you would get the following error:

Msg 34012, Level 16, State 1, Procedure sp_syspolicy_delete_condition, Line 33

Cannot delete Condition referenced by a Policy.

Example 1:

In this example, I would like to show we can drop a policy condition and policy category based on a policy name or policy id and policy condition name or policy condition id.

-- to get the details of a policy id and policy condition id

select policy_id,spc.condition_id FROM msdb.dbo.syspolicy_policies sp

INNER JOIN msdb.dbo.syspolicy_conditions spc

                ON sp.condition_id = spc.condition_id

where sp.is_system=0

and sp.name='StoredProcNamingConvention'

-- to delete a policy, either supply name or id

EXEC msdb.dbo.sp_syspolicy_delete_policy @name='StoredProcNamingConvention' --,@policy_id=98

GO

-- to delete a policy condition , either supply name or id

EXEC msdb.dbo.sp_syspolicy_delete_condition @name='ProcedureCondition'--, @condition_id=19

GO