Issue with table naming policy with PBM - can't create it passed in the condition or not

Question

I used PBM to create a condition to blocking create tables if nomenclature no haven't the prefix tbl:

EXEC msdb.dbo.sp_syspolicy_update_condition @condition_id=24, @description=N'Necessário acrescentar o prefixo ''tbl''', @facet=N'IMultipartNameFacet', @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>tbl%</Value>
  </Constant>
</Operator>', @is_name_condition=2, @obj_name=N'tbl%'
GO

I applied this condition into my tables in the DB "cadastro"

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=65, @enabled=False
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database/Sequence', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database', @condition_name=N''

...

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=72, @enabled=False
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database', @condition_name=N''


GO

EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=37, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000'

GO

I setted Evaluation Mode like On change: prevent.

If I try execute this script:

create table tblteste(
col1 int
);

SSMS will return this error:

Policy 'NomeDeTabela' has been violated by 'SQLSERVER:\SQL\XXX-XXX-XX\DEFAULT\Databases\cadastro\Tables\dbo.tblteste'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'tbl%''
Policy description: ''
Additional help: 'Ausência de prefixo 'tbl' no nome da tabela' : ''
Statement: 'create table tblteste(
col1 int
)'.
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.
The statement has been terminated.

The nomenclature of table meets established policy.

I followed step-by-step from Microsoft SQL Server documentation (https://learn.microsoft.com/en-us/sql/relational-databases/policy-based-management/lesson-2-create-and-apply-a-naming-standards-policy?view=sql-server-ver15) but the problem persist.

Name and version information:

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0 
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

Anywhone pass this problem before?

Issue with table naming policy with PBM - can't create it passed in the condition or not

0 Answers0