How to create a naming convention for a stored procedure using a SQL Server policy management?

Problem: How to create a naming convention for a stored procedure using a SQL Server policy management?

Solution: Administer ​Servers by ​Using ​Policy-​Based ​Management

Policy-Based Management is a policy based system for managing one or more instances of SQL Server. Use is to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects.

Use SQL Server management Studio to create and manage policies, to:

1.     Select a Policy-Based Management facet that contains the properties to be configured.

A facet can be anything in specific like table, view, index, function and stored procedure

2.     Define a condition that specifies the state of a management facet.

Choose a condition to address the facet whether it should follow or should not follow the values.

3.     Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

Evaluation modes. There are four evaluation modes; three can be automated:

Evaluation Mode	When to Check	Action upon Violation
On Demand	On demand, only	Users can choose to configure the system to comply to the policy
On Schedule	Periodically per the specified schedule	log
On Change – Log only	When there is a relevant (DDL) change to the DBMS	log
On Change – Prevent	When there is a relevant (DDL) change to the DBMS	Roll back the transaction

When automated policies are not enabled, Policy-Based Management will not affect system performance.

4.     Check whether an instance of SQL Server follows the policy.

For failed policies, Object Explorer indicates a critical health warning as a red icon next to the target and the nodes that are higher in the Object Explorer tree.

Example 1:

In this example, I would like to show how this policy will act in case of creating a new procedure with a bad naming convention to see if that allows to create a procedure on a database.

create procedure sp_testset

as begin

select 1

end

OUTPUT:

 As we can see there is an error when a procedure with a name starting with sp_ it is blocked by the policy we just created and the conditions are prevented to rollback this transaction.