You are here:Home » tsql » Script for configuration Policy management

Script for configuration Policy management

---Create Fecat Condition for table for table can create as named like 'tbl%'















---Create Fecat Policy for above condition

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'TestPolicy_ObjectSet', @facet=N'Table', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'TestPolicy_ObjectSet', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'TestPolicy', @condition_name=N'TestCondition', @policy_category=N'', @description=N'Can create table like tbl%', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'TestPolicy_ObjectSet'
Select @policy_id
GO