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]