Updated: June 10, 2016
Displays one row for each Policy-Based Management policy in the instance of SQL Server. syspolicy_policies belongs to the dbo schema in the msdb database. The following table describes the columns in the syspolicy_policies view.
|Column name||Data type||Description|
|policy_id||int||Identifier of the policy.|
|name||sysname||Name of the policy.|
|condition_id||int||ID of the condition enforced or tested by this policy.|
|root_condition_id||int||For internal use only.|
|date_created||datetime||Date and time the policy was created.|
|execution_mode||int||Evaluation mode for the policy. Possible values are as follows:|
0 = On demand
This mode evaluates the policy when directly specified by the user.
1 = On change: prevent
This automated mode uses DDL triggers to prevent policy violations.
2 = On change: log only
This automated mode uses event notification to evaluate a policy when a relevant change occurs and logs policy violations.
4 = On schedule
This automated mode uses a SQL Server Agent job to periodically evaluate a policy. The mode logs policy violations.
Note: The value 3 is not a possible value.
|policy_category||int||ID of the Policy-Based Management policy category that this policy belongs to. Is NULL if it is the default policy group.|
|schedule_uid||uniqueidentifier||When the execution_mode is On schedule, contains the ID of the schedule; otherwise, is NULL.|
|description||nvarchar(max)||Description of the policy. The description column is optional and can be NULL.|
|help_text||nvarchar(4000)||The hyperlink text that belongs to help_link.|
|help_link||nvarchar(2083)||The additional help hyperlink that is assigned to the policy by the policy creator.|
|object_set_id||int||ID of the object set that the policy evaluates.|
|is_enabled||bit||Indicates whether the policy is currently enabled (1) or disabled (0).|
|job_id||uniqueidentifier||When the execution_mode is On schedule, contains the ID of the SQL Server Agent job that runs the policy.|
|created_by||sysname||Login that created the policy.|
|modified_by||sysname||Login that most recently modified the policy. Is NULL if never modified.|
|date_modified||datetime||Date and time the policy was created. Is NULL if never modified.|
When you are troubleshooting Policy-Based Management, query the syspolicy_conditions view to determine whether the policy is enabled. This view also displays who created or last changed the policy.
Requires membership in the PolicyAdministratorRole role in the msdb database.