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'',
