Wednesday, September 21, 2011

SQL Server - Create a Policy to Enfore Naming Convention on Stored Procedure

In SQL Server 2008, assuming we need to enforce a naming standard on stored procedures:
All stored procedures in the database (TestData) must start with "usp_" - ("usp" stands for user stored procedure).

Step 1 Create the Condition
  1.  In Object Explorer, expand Management, expand Policy Management, expand Conditions, right-click the Conditions node and then click New Condition.
  2.  On the Create New Condition, General page, in the Name box, type the name of the new condition: SP Naming
  3. Select the Multipart Name facet in the Facet box.
  4. In the Expression area, construct the condition expressions : @Name like 'usp_%'.
  5. On the Description page, type a description and then click OK.


Step 2 Create the Policy
  1. In Object Explorer, expand Management, expand Policy Management, right-click Policies, and then click New Policy.
  2. On the Create New Policy, General page, in the Name box, type the name of the new policy: SP Naming Policy.
  3. If you want the policy to be enabled as soon as it is created, select the Enabled check box.
  4. If the evaluation mode is On demand, the Enabled check box is not available.
  5. In the Check condition box, select the existing condition: SP Naming. To edit the condition, select the condition and then click the build (...) button.
  6.  In the Against targets box, select the Every StoredProcedure target. Create a new condition TestData DB Only in the Database Node (Facet: Database, Condition: @Name='testdata').
  7. In the Evaluation Mode box, select how this policy will behave: On Change: Prevent. Different conditions can have different valid evaluation modes.
  8. To limit the policy to subset of the target types, in the Server restriction box, select from limiting conditions or create a new condition.
  9. On the Description page, in the Category box, select an optional policy category or create a new one.
  10. Type n optional description of the policy.
  11.  Click OK.


When the policy is enabled, trying to create a procedure that violates the policy, the following message is received:

Changed database context to 'testdata'.
Policy 'SP Naming Policy' has been violated by 'SQLSERVER:\SQL\TUSNC012LKVT006\DEFAULT\Databases\testdata\StoredProcedures\dbo.HelloWorld3_proc'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp_%''
Policy description: ''
Additional help: '' : ''
Statement: '
create procedure HelloWorld3_proc
as
select 'HelloWorld'
'.
Msg 3609, Level 16, State 1, Server TUSNC012LKVT006, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

No comments: