I have previous written article about SQL SERVER Database Coding Standards and Guidelines Complete List Download. I just received question from one of the blog reader is there any way we can just prevent violation of company policy. Well Policy Management can come into handy in this scenario.
If our company policy is to create all the Stored Procedure with prefix ‘usp’ that developers should be just prevented to create Stored Procedure with any other prefix. Let us see small tutorial how to create conditions and policy which will prevent any future SP to be created with any other prefix.
Let us first create condition which defines that name of the database object should start with ‘usp’.
Now create policy for all the Stored Procedure of the database and assign previously created condition of naming convention to all the Stored Procedure of the database.
Make sure that Policy is enabled.
Now try to create Stored Procedure with incorrect name. It will not let you create it but will create error.
If you try to create Stored Procedure prefixed with ‘usp’ it will create it successfully.
Policy Management is very detailed subject and I will write more tutorial about this subject in future.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
How detailed can the policy management features of SQL Sever 2008 get? I have often run into an issue where the development staff created either a Constrain or Default in the table DDL but didn’t provide a name for it. Down the road when the decide a change is needed and they try to drop the contrain/default/PK they run into a snag since the Object has the random name given to it by MS. Can the new Policy Management allow me to disable this feature or at least modify it accordingly that the development staff will have to name each PK/Default/Constraint?
-Joseph
[...] Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes. (Read More Here) [...]