Policy Management is a powerful and often underappreciated capability within SQL Server. It allows database administrators to define and enforce policies to ensure compliance with best practices and organizational standards. In a recent blog post, I discussed the basics of Policy Management and how it can be used to improve the overall management of SQL Server instances.

One common question that arises when working with Policy Management is how to determine which policies are enabled on a server. Fortunately, SQL Server provides metadata views that allow us to query this information.

The syspolicy_target_sets and syspolicy_target_set_level views contain the necessary data to determine which policies are enabled on a server. The target set represents the target type (e.g., Database) and target skeleton (e.g., Server/Database). It includes all databases on the server, but can be further narrowed down using filters. The target set level contains the filter conditions.

To query this metadata and find the enabled policies on your database, you can use the following query:

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 are using SQL Server 2012 or later, you may also see AlwaysOn related policies included in the results. To filter the results to only show enabled policies, uncomment the last line of the query.

I am curious to know how many of you actively use Policy Management and for what reasons. Please share your thoughts and experiences in the comments section below.