Enforce Naming Conventions for Stored Procedures Using Policy Based Management in SQL Server

Enforce Naming Conventions for Stored Procedures Using Policy Based Management in SQL Server

1.      Problem

In many situations DBA may want to enforce naming conventions for database objects across multiple databases.

For example,

To restrict users to prefix "sp_"  for User Defined Stored procedure due to following reasons:

•           For performance:

The master db will always be checked first because of the prefix of sp_ which is time consuming.  All system defined stored procedure name starts with prefix "sp_". 

•           Possible conflict of user created stored procedures with system stored procedures:

If a user-defined stored procedure has the same name as a system stored proc, the user-defined procedure will never be executed.

2.      Solution:

DBA can restrict/force the users to follow stored procedure naming conventions by using Policy Based Management(PBM) in SQL Server.

·         An administrator run this policy and receives a list of stored procedures that are out of compliance.

·         If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

Few other Scenarios:

·         1: To restrict users to prefix "sp_"  for User Defined Stored procedure

·         2:To follow a particular naming convention for procedures Eg ‘usp_’

·         3: A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.

·         4: The AdventureWorks2012 database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

·         5 : Enforce SQL Server Database Naming Conventions. Standard naming convention for databases would likely include the following rules:

·         Does not include any special characters CREATE DATABASE [embedded.period]

·         Does not start with a number; Eg: CREATE DATABASE [1_start_number];

·         Does not have embedded or trailing spaces; CREATE DATABASE [trailing_space ]

·         On demand policy to ensure that all the databases have the Auto Shrink option set to True. By default, a database that is created has Auto Shrink set to False, as shown in the figure below.

3.      Benefits:

• Policy Based Management is considered as one of ‘standard’ method to administer database proactively.
• It allows DBAs to create various policies to enforce certain rules to single database, to group of databases or to whole server.
• It is possible to combine more than one condition into one policy.
• Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system.
• It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

4.      Limitation:

·         Policies can affect how some SQL Server features work.

For example, change data capture and transactional replication both use the systranschemas table, which does not have an index. If you enable a policy that all tables must have an index, enforcing compliance of the policy will cause these features to fail.

·         Certain policies can’t be enforced i.e. they will not have the option “On Change : Prevent” applied to them and so you won’t get the option in evaluation Mode drop down at the bottom

5.      Steps:  How-To

Policies are created and managed by using Management Studio.
The process includes the following steps:
Step 1:
Right click on Management -> Policy Management -> Policies ->  then click on New Policy:

Step 2: In create New policy window:

·         Name: Specify the name of policy.

·         Check Condition:

Condition Criteria that specifies the state of facet to true or false. With help of facets you can create different conditions to monitor.

Click on ‘New Condition’. This will allow you to create a new condition for the policy.
When SQL Server collations do not exactly match Windows collations, test your condition to determine how the algorithm resolves conflicts.

Step 3:

In the Create New Condition window:

·         Name: Specify a name for the condition

·         Facets:

A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database

Here Choose ‘Stored Procedure’ in the drop down.

·         Expression:

Field: On which property we want to put

condition. Select @Name which indicate the stored procedure name

·         Operator: Choose NOT LIKE operator.

·         Value: Here specify the conditions to check against the stored procedure names

'sp_%'  :Name of the SPs which start with ‘sp_’

‘[0-9]%’: Name of the SPs which start with a digit

·         AND Or: Select AND

Now click OK button.

2.1

If you want to select any existing condition follow the below steps:

In the drop down list-Stored Procedure-Select the condition

Step 2.2 Against targets:

·         Against targets: Select the DB object to be evaluated.

Here you can select ‘Every’ – ‘Stored Procedures’.

If you want to select specific stored procedures to evaluate you can create a new condition here by clicking on ‘New Condition’

2.3 Database selection

Next select the database you want to evaluate.

You can select ‘Every’-‘Database’

If you want to select a particular database you can create a new condition here by clicking on ‘New Condition’. Also you can select an existing ‘Condition’ too.

2.4 Evaluation modes:

There are four evaluation modes, three of which 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 according to 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

2.4.1 On demand: This mode evaluates the policy when directly specified by the user.  Choose ‘On Demand’.

2.4.2 Evaluation Mode - On schedule:

This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

Enable to run the Policy on s scheduled time. You can create a new schedule by clicking on Schedule->New . Or can pick an existing schedule by clicking on Schedule->Pick

2.4.3 Evaluation Mode - On change : log only

This automated mode uses event notification to evaluate a policy when a relevant change is made.

2.4.4 Evaluation Mode - On change : prevent

‘Enable’ the newly created policy

The policy prevent users from creating a new stored procedure with prefix ‘sp_’

Step 3 Evaluate the Policy:

To evaluate the policy, go to Object Explorer, expand Management > Policy Management > Policies, right-click the Policy, and choose Evaluate.

If any stored procedure in the server violates your naming convention, they should appear in the list with a red X indicating failure:

You can click on the "View..." link in each row, and see exactly which part of the condition caused the  failure.

The Results Detailed View shows the procedure named sp_ActivityLog doesn’t adhere to the naming convention.

6.      Metadata:

Database: msdb

Db objects:

msdb .dbo.syspolicy_conditions
	syspolicy_conditions_internal
	syspolicy_execution_internal
	syspolicy_facet_events
	syspolicy_management_facets
	syspolicy_policies
	syspolicy_policies_internal
	syspolicy_policy_categories
	syspolicy_policy_execution_history
	syspolicy_policy_execution_history_details
	syspolicy_policy_execution_history_details_internal
	syspolicy_policy_execution_history_internal
	syspolicy_target_sets
	syspolicy_target_sets_internal

Querying meta data:

USE MSDB

GO

--/* What policies exist on the server? */

SELECT p.name AS 'policy_name',

       p.is_enabled,

       p.execution_mode,

       c.name AS 'condition_name',

       c.facet,

       p.date_created

FROM syspolicy_policies p

     INNER JOIN syspolicy_conditions c

     ON p.condition_id = c.condition_id

ORDER  BY p.name

--/* Execution history of policy violations */

SELECT

            h.history_id,

            pp.name  'policy name',

            pp.execution_mode,

            h.result,

            h.start_date,

            h.end_date,

            d.target_query_expression,

            CAST(d.result_detail AS XML) 'result'

FROM syspolicy_policies AS pp

INNER JOIN syspolicy_policy_execution_history  h

ON h.policy_id = pp.policy_id

INNER JOIN syspolicy_policy_execution_history_details  d

ON d.history_id = h.history_id

ORDER  BY h.history_id

Summary

Policy-Based Management is designed to accomplish the following goals:

• Help DBAs to define standard policies for an organization's SQL Server instances.
• Help DBAS to granularly select what instances, databases, and objects are to be affected by policies.
• Helps DBA to enforce established policies.
• Helps DBAs to correct out of compliance policies.