Policy Based Management

Question

Policy Based Management

wanox

Ten Centuries

Points: 1083
More actions
June 12, 2012 at 10:08 pm

#392504

Is it possible to specify more than one target in Policy based management. I am trying to enforce dbo schema in 5 different databases but it seems like i would have to create a different condition for each database. Is there a way i can use one condition for all 5 databases. Or possibly limit the rule to the Windows group?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

You would create a condition on the database facet, for the name object and give it the names of the DB's you want it to check, then it uses that condition to check the DB's for that particular policy.

wanox Ten Centuries Points: 1083 More actions · Answer 2

I create a condition on the database facet which has 3 database names. I also create another condition on the Multipart naming and choose the @SchemaName = 'dbo'. I use the Database names condition as my target and select "On change:prevent" as the Evaluation mode. I get this error "Operation 'Create' on object ObjectSet[@Name = 'Enforce dbo Schema_ObjectSet'] failed during execution. Violation of UNIQUE KEY constraint 'UQ_syspolicy_objects_sets_name'. The duplicate key value is (Enforce_dbo_Schema_ObjectSet).

wanox Ten Centuries Points: 1083 More actions · Answer 3

I was able to delete the Enforce dbo objectset from [msdb].[dbo].[syspolicy_object_sets_internal]. When i tried creating the policy again i got this error: Object Set 'Enforce dbo Schema_objectset does not support Evaluation Mode 'On change:prevent'

Ant-Green SSC Guru Points: 113509 More actions · Answer 4

Then it looks like you have created two object with the same name, which will break the unique key.

You first need to create your multipart identifier condition.

Then create the policy which enforces the multipart condition.

Within the targets, use the first target (sequence) and change the In every database to a new condition on the database facet, name object and the db names.

Then within the other in every database just select the DB condition.

wanox Ten Centuries Points: 1083 More actions · Answer 5

Dit what you suggested. Created the multipart condition and a DB condition that has all the databases, in the target box i choose the DB condition but i get the same error:

Object does not support Evaluation mode 'On Change:Prevent'.

wanox Ten Centuries Points: 1083 More actions · Answer 6

When I limit the DB condition to have only one database in the list, it works. But it wont let me list more than one database

Ant-Green SSC Guru Points: 113509 More actions · Answer 7

I would say you will need to create a custom DDL trigger which rolls back the change if they try to create an object in a schema which is not DBO. Apply it to all server and filter inside the trigger for the DB's you want to enforce the policy against.

wanox Ten Centuries Points: 1083 More actions · Answer 8

Thanks for staying with me on this. I thot there was way i could avoid using triggers.

Ant-Green SSC Guru Points: 113509 More actions · Answer 9

On Change : Prevent policies create triggers so your not doing anything different as to what you where trying to do in the first place, its just it doesn't look like your creating a trigger in PBM.

wanox Ten Centuries Points: 1083 More actions · Answer 10

You might be right on that. I'd use this to support my creation of creating a custom trigger. Thanks again.