10

In SQL Server 2012, I had a policy set to not allow spaces in a table name. However, when I use the same policy in SQL Server 2016, I get an error.

Here is the code for the condition:

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No Spaces', @description=N'No spaces in table names.', @facet=N'IMultipartNameFacet', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>NOT_LIKE</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>String</TypeClass>
    <Name>Name</Name>
  </Attribute>
  <Constant>
    <TypeClass>String</TypeClass>
    <ObjType>System.String</ObjType>
    <Value>% %</Value>
  </Constant>
</Operator>', @is_name_condition=4, @obj_name=N'% %', @condition_id=@condition_id OUTPUT
SELECT @condition_id

Here is the code for the policy:

DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Table Names_ObjectSet', @facet=N'IMultipartNameFacet', @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'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Sequence', @type=N'SEQUENCE', @enabled=False, @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', @level_name=N'Database', @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/Sequence', @level_name=N'Sequence', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=False, @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', @level_name=N'Database', @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/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Synonym', @type=N'SYNONYM', @enabled=False, @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', @level_name=N'Database', @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/Synonym', @level_name=N'Synonym', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_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', @level_name=N'Database', @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/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/UserDefinedFunction', @type=N'FUNCTION', @enabled=False, @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', @level_name=N'Database', @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/UserDefinedFunction', @level_name=N'UserDefinedFunction', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/UserDefinedType', @type=N'TYPE', @enabled=False, @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', @level_name=N'Database', @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/UserDefinedType', @level_name=N'UserDefinedType', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=False, @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', @level_name=N'Database', @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/View', @level_name=N'View', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/XmlSchemaCollection', @type=N'XMLSCHEMACOLLECTION', @enabled=False, @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', @level_name=N'Database', @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/XmlSchemaCollection', @level_name=N'XmlSchemaCollection', @condition_name=N'', @target_set_level_id=0


GO

DECLARE @policy_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Table Names', @condition_name=N'No Spaces', @policy_category=N'', @description=N'', @help_text=N'', @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'Table Names_ObjectSet'
SELECT @policy_id


GO

In SQL Server 2012 and 2014, this gives the expected results:

CREATE TABLE [test table]
(Id INT NULL)

Policy 'Table Names' has been violated by 'SQLSERVER:\SQL\LSRSQL07\SQL2012\Databases\test\Tables\dbo.test table'. This transaction will be rolled back. Policy condition: '@Name NOT LIKE '%[- .]%' AND @Name NOT LIKE '%[^A-Za-z0-9[_]]%'' Policy description: '' Additional help: '' : '' Statement: 'CREATE TABLE [test table] (Id INT NULL) '. Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 [Batch Start Line 48] The transaction ended in the trigger. The batch has been aborted.

And if I run the following code, I get no error:

CREATE TABLE [testtable]
(Id INT NULL)

However, if I run any CREATE TABLE statement, with the policy enabled, on SQL Server 2016, I get the following error:

Policy 'Table Names' has been violated by 'SQLSERVER:\SQL\LSRSQL07\SQL2016\Databases\test\Tables\dbo.testtable'. This transaction will be rolled back. Policy condition: '@Name NOT LIKE '% %'' Policy description: '' Additional help: '' : '' Statement: 'CREATE TABLE [testtable] (Id INT NULL)'. Msg 515, Level 16, State 2, Procedure sp_syspolicy_execute_policy, Line 69 [Batch Start Line 44] 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.

In SQL Server 2016, I cannot create any table, whether it passes the condition or not.

This is SQL Server 2016, SP1, CU3.

Any ideas on this?

Edit: I am needing the evaluation mode to be "On change: prevent"

| improve this question | |
6
+500

Tested the scripts on a SQL Server 2016 SP1 CU2 instance and the policy works if the Evaluation mode is set to "On Change : Prevent". (there is a bug that doesn't let you evaluate policies that use specific facets).

Meanwhile, if you only use the policy for table names, you can also try the "Table Option" facet instead of the "MultipartName", with the same configuration (@NAME NOT LIKE '% %').

| improve this answer | |
  • If I set the evaluation mode to "On Demand", it does work, but to be fair, I hadn't tried that previously. I would prefer to have this On change: prevent to keep people from creating tables and then stored procs referencing the badly named tables. – John Jun 1 '17 at 14:46
  • For me it does not work to set the evaluation mode to "On demand" and evaluate the policy manually. But it works fine if the evaluation is set to "On Change:Prevent" and try to create tables. You could try to post the the problem on Microsoft Connect to find out if it is a bug or not. – Dragos Jun 6 '17 at 9:07
  • Thanks, @Dragos. Is it happening on any table, even those that should pass the condition? – John Jun 6 '17 at 15:46
  • Tables that have no white spaces in the name get created successfully, and those that have white spaces fail with a policy violation error. – Dragos Jun 7 '17 at 12:22
  • We are having the same issue with stored procedure and view facets. We are on the SQL 2016 SP1 CU3 (Latest). Like John stated, this seems like a bug but was wondering if anyone was able to figure out a workaround? – DBAuser Jul 17 '17 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.