How to Create Condition and Policy in SQL Server 2012 / Create a Policy for Naming Convention of Stored Procedure

This article will cover most salient feature of SQL Server, Policy based administrative management of SQL Server to create a condition at server level.
In almost all of the projects we follow some standards for naming convention, so to avoid redundant naming of objects like stored procedure we can create a policy for naming convention.
I have been working with SQL Server from past one year and we always follow naming convention, so it will be better if admin create one policy for naming convention so that no developer can miss it.

Policies

Policy-Based Management is a new management feature introduced in SQL Server 2008. Policy-Based Management allows Database Administrators to define a set of policies that can control many different aspects of SQL Server. Policies can be applied to a single server or to groups of servers. For example, a Database Administrators could define a policy that specifies how a particular configuration option should be set on all the servers in the enterprise.
Before creating policy, first we will create one condition that will be used in policy

How to create Condition

Following are the steps to create condition in SQL Server

1. Go to Management -> Conditions as show below

2. Right click on Condition and click on New Condition, a new window will appear as show below

3. Fill all the fields

1. Write any name in the Name field (for example: StoredProcNamingConvention)
2. Select ‘Stored Procedure’ in the Facet drop down list
3. Keep AndOr blank because this single condition
4. Select Operator LIKE in drop down list
5. Write rule in the value column enclosed with single quotes (for example: ‘usp%’)

Note: This rule is to start stored procedures with ‘usp’, if we want stored procedure to start with ‘usp_’ the value should be ‘usp[_]%’

4. After clicking on OK button condition will be created

How to create Policy

5. Now right click on Policy and click on New Policy

6. New Policy window will open, fill all the fields

1. Enter name of policy
2. Enable it by checking Enabled checkbox
3. Select condition created for naming convention of stored procedure
4. Select Evaluation Mode as On change: prevent
5. Server restriction as None

7. After Clicking on OK button policy will be created

8. Now if we execute following script to create stored procedure ‘sp_MyStoredProc’

CREATE PROCEDURE [dbo].[spMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END

Message from SQL Server for violating policy:

Policy 'StoredProcNamingConventionPolicy' has been violated by 'SQLSERVER:\SQL\ABCServer\DEFAULT\Databases\JPDB\StoredProcedures\dbo.spMyStoredproc'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp%''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE PROCEDURE [dbo].[spMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

9. But if we write stored procedure name which start with ‘usp’, then script will be executed successfully

CREATE PROCEDURE [dbo].[uspMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END

Message