SQL SERVER – Finding What Policies Are Enabled on Our Databases

When I wrote about Policy Management last week (SQL SERVER – Introduction to Policy Management), lesser did I know I will get queries from blog readers on the basics. As I always said, Policy Management is always an underappreciated capability inside SQL Server. In one of the mails, I was asked – “How do I know which policy is enabled on my Server?” Before I can answer the same with a simple query to our metadata, let me walk through what happens in words before going into the solution.

When SQL Server “policy engine” executes a policy, it determines which objects to evaluate against the policy’s condition. It uses the target set and target set level data to determine this. The information can be viewed in the syspolicy_target_sets and syspolicy_target_set_level views. A target set has data for the target type (for example, [Database]) and target skeleton (for example, [Server/Database]). This target skeleton includes all databases on the server. The target set can be further narrowed with a filter. This filter can be another condition. This is the data stored in the target set level. Below is a query to help see this metadata.

/* Query the metadata to find the Policy, Condition, Target Set informations on our database */
SELECT p.policy_id,
p.is_enabled,
p.name AS 'policy_name',
c.condition_id,
c.name AS 'condition_name',
c.expression AS 'condition_expression',
ts.target_set_id,
ts.TYPE,
ts.type_skeleton,
tsl.condition_id AS 'target_set_condition_id'
FROM msdb.dbo.syspolicy_policies p
INNER JOIN msdb.dbo.syspolicy_conditions c
ON p.condition_id = c.condition_id
INNER JOIN msdb.dbo.syspolicy_target_sets ts
ON ts.object_set_id = p.object_set_id
INNER JOIN msdb.dbo.syspolicy_target_set_levels tsl
ON ts.target_set_id = tsl.target_set_id
-- WHERE p.is_enabled <> 0 -- Use this to get only enabled Policies on the DB


If you plan to run these on a SQL Server 2012+ server box, then you are likely to see the AlwaysOn related policies for reference. Go ahead and uncomment the last line to see only policies that are enabled on your SQL Server box. I would be curious to know how many of you actively use this capability and for what reasons. Do let me know via your comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)