PBM: Inconsistent handling of conditions for databases to enforce policies in - by Erland Sommarskog

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 377980 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/27/2008 3:20:23 PM
Access Restriction Public


Say that we want to add a rule to PBM that no stored procedures should start sith sp_.
Policy->New Policy, add a condition for Stored Procedures where @Name NOT LIKE 'sp[_]%'
Enable policy and select On Change Prevent. Against Every StoredProcedure in Every Database? Hm, we should exclude master, select NewCondition and enter the condition
@Name != 'master'. Press OK, and test:

USE tempdb
create procedure sp_test as print 1
Policy 'Namecheck' has been violated by 'SQLSERVER:\SQL\YAZORMAN\IKI\Databases\tempdb\StoredProcedures\dbo.sp_test'.

Good! (The policy in this shape is attached.)

But wait! There are system procedures in msdb as well. Change the condition "DBs to
Check" and add the condition @Name != 'msdb'. Press OK. Rerun the test:
"Command(s) completed successfully."


Open the policy? Could it somehow have been disabled? Change to Every database,
press OK (why is there not an Apply button?). Open again and change the condition
to DBs to check again. Press OK. Now we get a validation error. Clicking on the red
cross in the upper-left corner I get:

TITLE: Microsoft SQL Server Management Studio

Validation failed for Policy 'Namecheck'. (Microsoft.SqlServer.Dmf)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=ObjectValidationException&EvtID=Policy&LinkId=20476


Exception of type 'Microsoft.SqlServer.Management.Dmf.ConflictingPropertyValuesException' was thrown.
Object Set 'Namecheck_ObjectSet' does not support Evaluation Mode 'On change: prevent'. 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=ConflictingPropertyValuesException&LinkId=20476



But as to why this failed there is not really any good explanation. Looking in Books
Online, I cannot find any useful information.

The same thing happens with similar conditions @Issytemobject = FALSE, @ID > 5.

There are plenty of problems here.

1) The most grave is that when I changed the condition for selecting the target, the
    policy silently stopped working.
2) The behaviour is inconsistent. Why is permitted to exclude one database, but not
3) The error message is useless in telling me what is wrong.
4) There is nothing documented on what works or not.

Sign in to post a comment.
Posted by Microsoft on 11/13/2008 at 12:17 PM
This is By Design.

Product Rationale:

In a policy you can use a condition to filter the set of objects the policy applies to. Theoretically, you can use any condition to filter out the targets. However, there is a tradeoff between flexibility and performance. The performance penalty in allowing arbitrary filter conditions becomes significant with “Check On Change” evaluation modes since the policy evaluation is done alongside the transaction. It is less an issue for “Check on Schedule” since users can choose to run the policy at off-peak time. Hence we made a tradeoff:

1)     For the common use cases where the filter is a “name condition” on Database, that is: <@name, [Operator], [value]>, we allow the full set of applicable evaluation modes because there is an efficient way in calculating the target set.

2)     For the use cases where arbitrary filter conditions are needed, we allow the flexibility but for performance reason we limit the evaluation mode to Check on Schedule

The syspolicy_conditions_internal.is_name_condition indicates if this condition is a “name condition” (when the value is not 0).

Is_name_condition has the following enum values (the operator in the filter condition):

0 == None

1 == Equals

2 == Like

3 ==NotEqual

4 == NotLike

Another point is about @IsSystemObject. If you don’t specify a value, the default behavior of PBM is to exclude system objects.

The documentation is not comprehensive enough due to tight UE resource in Katmai. We are working on that…