All stored procedures in the database (TestData) must start with "usp_" - ("usp" stands for user stored procedure).
Step 1 Create the Condition
- In Object Explorer, expand Management, expand Policy Management, expand Conditions, right-click the Conditions node and then click New Condition.
- On the Create New Condition, General page, in the Name box, type the name of the new condition: SP Naming
- Select the Multipart Name facet in the Facet box.
- In the Expression area, construct the condition expressions : @Name like 'usp_%'.
- On the Description page, type a description and then click OK.
Step 2 Create the Policy
- In Object Explorer, expand Management, expand Policy Management, right-click Policies, and then click New Policy.
- On the Create New Policy, General page, in the Name box, type the name of the new policy: SP Naming Policy.
- If you want the policy to be enabled as soon as it is created, select the Enabled check box.
- If the evaluation mode is On demand, the Enabled check box is not available.
- In the Check condition box, select the existing condition: SP Naming. To edit the condition, select the condition and then click the build (...) button.
- 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').
- In the Evaluation Mode box, select how this policy will behave: On Change: Prevent. Different conditions can have different valid evaluation modes.
- To limit the policy to subset of the target types, in the Server restriction box, select from limiting conditions or create a new condition.
- On the Description page, in the Category box, select an optional policy category or create a new one.
- Type n optional description of the policy.
- 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:
Post a Comment