Sql-server – Why SP_Naming policy still blocked the correct creating?

policy-based-managementsql server

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'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>LIKE</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>String</TypeClass>
    <Name>Name</Name>
  </Attribute>
  <Constant>
    <TypeClass>String</TypeClass>
    <ObjType>System.String</ObjType>
    <Value>usp_%</Value>
  </Constant>
</Operator>', @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,

Best Answer

After some testing with his code in Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 17134: ) , I could see he's facing the following error. Which is reported by Microsoft here

Msg 515, Level 16, State 2, Procedure msdb.sys.sp_syspolicy_execute_policy, Line 69 [Batch Start Line 0] Cannot insert the value NULL into column 'target_query_expression', table 'msdb.dbo.syspolicy_policy_execution_history_details_internal'; column does not allow nulls. INSERT fails.

Resolution This issue is fixed in the following cumulative updates for SQL Server:

Cumulative Update 2 for SQL Server 2017

Cumulative Update 6 for SQL Server 2016 SP1

Disclaimer: I did a pure guess until he updates the question with the error if it's not I will delete the answer