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 :

• http://technet.microsoft.com/en-us/library/bb522505(v=sql.105).aspx

• http://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

• http://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx?Redirected=true

• http://technet.microsoft.com/en-us/library/bb510667.aspx

• http://technet.microsoft.com/en-us/library/bb510408(v=sql.105).aspx

• http://technet.microsoft.com/en-us/library/dd938891(v=sql.100).aspx

• http://blogs.msdn.com/b/sqlserverfaq/archive/2012/01/19/policy-based-management.aspx

See Also :

• SQL Server - Miscellaneous