Policy-Based Management Execution Mode

If you are on a SQL 2008 or later then you are probably aware by now of the ability to set up various policies to help manage/audit, and in some cases prevent or roll back changes.

However you may get this far and wonder why the execution mode you want to use is not available to you:

Why can you not use On change prevent/On change: log only with all the facets?

The answer is quite simple:

All facets support On demand and On schedule. Facets support On change: log only if the change of the facet state can be captured by some events. Facets support On change: prevent if there is transactional support for the DDL statements that change the facet state. http://msdn.microsoft.com/en-us/library/bb510667.aspx

Now all we need is a list of which execution are available for each facet:

--http://jongurgul.com/blog/policy-based-management-execution-mode/
SELECT
 f.[name] [FacetName]
,ISNULL(p.d,'') [On change: prevent]
,ISNULL(l.d,'') [On change: log only]
,ISNULL(ds.d,'') [On demand / On schedule]
FROM msdb..syspolicy_management_facets f
LEFT OUTER JOIN (SELECT 'x' d,1 n) p ON f.execution_mode & p.n = p.n
LEFT OUTER JOIN (SELECT 'x' d,2 n) l ON f.execution_mode & l.n = l.n
LEFT OUTER JOIN (SELECT 'x' d,4 n) ds ON f.execution_mode & ds.n = ds.n
ORDER BY f.[name]