Sample Header Ad - 728x90

Why SP_Naming policy still blocked my correct creating?

-1 votes
1 answer
83 views
I used PBM to manage my policy against my user DBs. The Condition is: Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'SP_Naming_convention', @description=N'', @facet=N'StoredProcedure', @expression=N' Bool LIKE 2 String Name String System.String usp_% ', @is_name_condition=2, @obj_name=N'usp_%', @condition_id=@condition_id OUTPUT Select @condition_id GO The policy is: Declare @object_set_id int EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'SP_Naming_ObjectSet', @facet=N'StoredProcedure', @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'SP_Naming_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @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/StoredProcedure', @level_name=N'StoredProcedure', @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'SP_Naming', @condition_name=N'SP_Naming_convention', @policy_category=N'', @description=N'Get out of my box you DEV!', @help_text=N'Stop doing this!', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'SP_Naming_ObjectSet' Select @policy_id GO I tried to create an SP named usp_whatever: create proc usp_whatever as begin select @@VERSION end But the policy still blocked mine... Any thought? Thanks,
Asked by DBALUKE HUANG (439 rep)
Mar 24, 2019, 05:02 AM
Last activity: Mar 25, 2019, 02:02 AM