Mar 4, 2013

Sql server restrict the stored procedure naming convention


We can restrict or forced the users to follow stored procedure naming convention by using policy based management in sql server. I am explaining it by using sql server management studio (SSMS). In this example user must have to keep prefix usp_ in the name of stored procedure in the specific database.

Step 1: Right click on Management -> Policy Management -> Policies and then click on New Policy:

 Step 2: In create New policy window:
Name: We can write any name of policy. In this example we are writing Stored Procedure Naming Rule.
Now we are going to click on new condition in the check condition drop down to add the conditions.


Step 3: In the create new condition window:
Name: We can write any name of the condition. In this example we are keeping Procedure Name Condition.
Facets: Choose stored procedure in the drop down.
Expression:
Field: On which property we want to put condition. In this example we are choosing @Name which implies name of the stored procedure.
Operator: in this example we are choosing LIKE operator.
Value: 'usp_%'


Now click on the OK button.
Step 4: In this previous window that is create new policy window:
Enabled: We are checking it to enable it after its creation.
Evaluation Mode: In this example we are choosing On Change:Prevent. It means this event will occur if user will try to create or alter stored procedure name.


Step 5: This naming conditions is applicable in the all the database. If we want we can put this restriction in the specific database. For this click on the Every drop down for database and a new conditions. It will open new condition window. Write followings:
Field: @Name
Operator: IN
Value: Name of databases. In this example: Array('Exact_help','Employee')


Then click on OK buttons.

Now we are testing it by creating the stored procedure which doesn't follow the naming convention:

CREATE PROCEDURE GetData
AS
SELECT 1

We will get error message like:

Policy 'Stored Procedure Naming Rule' has been violated by ''.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp_%''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE PROCEDURE GetData
AS
SELECT 1'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

Means it is working fine. Now I am creating same stored procedure with correct naming convention:

CREATE PROCEDURE usp_GetData
AS
SELECT 1

It has created successfully. We can also created this policy using transact sql:

DECLARE @object_set_id INT
DECLARE @target_set_id INT

--Creating Policy
EXEC msdb.dbo.sp_syspolicy_add_object_set
@object_set_name=N'Stored Procedure Naming Rule_ObjectSet',
@facet=N'StoredProcedure',
@object_set_id=@object_set_id OUTPUT
--

EXEC msdb.dbo.sp_syspolicy_add_target_set
@object_set_name=N'Stored Procedure Naming Rule_ObjectSet',
@type_skeleton=N'Server/Database/StoredProcedure',
@type=N'PROCEDURE',
@enabled=True,
@target_set_id=@target_set_id OUTPUT
EXEC msdb.dbo.sp_syspolicy_add_target_set_level
@target_set_id=@target_set_id,
@type_skeleton=N'Server/Database/StoredProcedure',
@level_name=N'StoredProcedure',
@condition_name=N'',
@target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level
@target_set_id=@target_set_id,
@type_skeleton=N'Server/Database',
@level_name=N'Database',
@condition_name=N'',
@target_set_level_id=0

GO

DECLARE @policy_id INT

EXEC msdb.dbo.sp_syspolicy_add_policy
@name=N'Stored Procedure Naming Rule',
@condition_name=N'Procedure Name Condition',
@execution_mode=1, @is_enabled=True,
@policy_id=@policy_id OUTPUT,
@object_set=N'Stored Procedure Naming Rule_ObjectSet'
GO

--Putting the naming restriction
DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
@name=N'Procedure Name Condition',
@description=N'',
@facet=N'StoredProcedure',
@expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>LIKE</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>usp_%</Value>
</Constant>
</Operator>',
@is_name_condition=2,
@obj_name=N'usp_%',
@condition_id=@condition_id OUTPUT

--Putting the database list restriction
DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
@name=N'DbList',
@description=N'',
@facet=N'Database',
@expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>IN</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Function>
<TypeClass>Array</TypeClass>
<FunctionType>Array</FunctionType>
<ReturnType>Array</ReturnType>
<Count>2</Count>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>Exact_Help</Value>
</Constant>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>Employee</Value>
</Constant>
</Function>
</Operator>',
@is_name_condition=0,
@obj_name=N'',

No comments:

Post a Comment