SQL Server - Policy based management - Enforcing naming convention for Database and its objects

This post is about enforcing naming convention for database and its objects through Policy based management in SQL Server .

Naming convention can be enforced for below list of objects :

Note : But Policy evaluation mode differs for each object .

APPLICATION ROLE
ASYMMETRIC KEY
CERTIFICATE
DATABASE ROLE
DEFAULT
INDEX
RULE
SCHEMA
SQL ASSEMBLY
STORED PROCEDURE
SYMMETRIC KEY
SYNONYM
TABLE
TRIGGER
USER
USER DEFINED FUNCTION
USER DEFINED TYPE
VIEW
XML SCHEMA COLLECTION


In this post , let us see an example for enforcing naming convention while creating Stored procedure .



Create New Condition :

Under Object Explorer - > Management - >  Policy Management - > Conditions - > New Condition ..



Create New policy :

Under Object Explorer - > Management - >  Policy Management - > Policies - >  New Policy ...

  • Mention the Policy name .
  • Check the option Enabled for the policy to be in enabled state .
  • Check Condition : point to the above created condition .
  • Evaluation Mode : On change : prevent.



Now if I try to create Stored procedure with name starting with 'SP_ ' , the above created policy will
prevent  from creating Stored procedure saying policy has been violated .

 


References :


See Also :


No comments: