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
Last activity: Mar 25, 2019, 02:02 AM