540
Part V: Enterprise Data Management
■ (^) On Change — Log Only: The policy is evaluated when the facet is changed, and
violations are logged.
■ (^) On Change — Prevent: The policy is evaluated when the facet is changed, and vio-
lations are rolled back. This action rolls back a violation only after the action has
occurred; meaning a large transaction that violates a policy may take a long time
to roll back changes.
Every facet may be set to On Demand or On Schedule, but On Change is limited. PBM relies
on DDL eventing to do CheckOnChange, and not all objects support DDL eventing. The fol-
lowing query reports which Execution modes are available for which facets:
SELECT name as Facet,
Max(CASE WHEN execution_mode & 0 = 0 Then 1 else 0 End)
as 'On Demand',
Max(CASE WHEN execution_mode & 4 = 4 Then 1 else 0 End)
as 'On Schedule',
Max(CASE WHEN execution_mode & 2 = 2 Then 1 else 0 End)
as 'On Change Log Only',
Max(CASE WHEN execution_mode & 1 = 1 Then 1 else 0 End)
as 'On Change Prevent'
FROM msdb.dbo.syspolicy_management_facets
GROUP BY name
ORDER BY name
Result is shown in Table 20-1:
TABLE 20 -1 Facet Listing
Facet
On
Demand
On
Schedule
On Change
Log Only
On Change
Prevent
ApplicationRole 1 1 1 1
AsymmetricKey 1 1 1 1
Audit 1 1 0 0
BackupDevice 1 1 0 0
BrokerPriority 1 1 0 0
BrokerService 1 1 0 0
Certifi cate 1 1 0 0
Computer 1 1 0 0
Credential 1 1 0 0
CryptographicProvider 1 1 0 0
Database 1 1 0 0
DatabaseAuditSpecifi cation 1 1 0 0
c20.indd 540c20.indd 540 7/30/2012 5:45:17 PM7/30/2012 5:45:17 PM
http://www.it-ebooks.info