SQL SERVER – 2008 – Introduction to Policy Management – Enforcing Rules on SQL Server

I have previously written article about SQL SERVER Database Coding Standards and Guidelines Complete List Download. I just received a question from one of the blog reader is there any way we can just prevent violation of company policy. Well Policy Management can come in 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 a 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 the conditions which defines that name of the database object should start with ‘usp’.

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy1

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy2s

Solarwinds

Now create policy for all the Stored Procedure of the database and assign previously created a condition of naming convention to all the Stored Procedure of the database.

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy3

Make sure that the Policy is enabled.

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy4s

Now try to create Stored Procedure with incorrect names. It will not let you create it, but will create an error.

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy5

If you try to create Stored Procedure prefixed with ‘usp’ it will create it successfully.

SQL SERVER - 2008 - Introduction to Policy Management - Enforcing Rules on SQL Server policy6

Policy Management is a very detailed subject and I will write more tutorial about this subject in future.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – 2008 – Step By Step Installation Guide With Images
Next Post
SQLAuthority News – 600 Article and Over 3 Million Readers

Related Posts

13 Comments. Leave new

  • 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

    Reply
  • Can I backup Policies created on one server and restore it on another server ?

    Reply
  • I have concern here…
    When you click on Database Diagram folder it won’t allow you to create the system SPs required for diagram just because it violates the Policy.

    Question…
    What could be the solution to skip system objects?

    Thanks,
    Abhijit

    Reply
  • Thulani Mathenjwa
    May 24, 2010 11:55 am

    Hi Guys

    Please help, i need to know how to enable Policy Management in SQL Sever 2008. Is there a Service that i need to start or what ?

    Reply
  • i have a doubt .. using policy management we can restrict the users to follow a particular naming conventions for procedures , and thats fine .. but we cant do the same thing for table names , foreign key constraints names etc(i tried to set up this one but i got only two evaluation modes (on schedule and on demand) i didnt get on change : prevent ) is there a way to implement this in policy management ..

    Reply
  • Is there any built in feature to disable or enable triggers in sql server 2008

    Reply
  • Is there any in- built feature to disable or enable triggers in sql server 2008
    please give reply pinal

    Reply
  • Reply
  • Is there a policy that can be made if a stored procedure runs longer than 1 minute?

    Reply
  • bhaveshdesai007
    May 13, 2013 4:57 pm

    Can u plz suggest how to enforce naming convention for Tables in all databases, i tried it the way you have given above .
    while making condition i have take Table as facet but while making a policy i m not geting the On Change prevent option .
    please reply

    Reply
  • @bhaveshdesai007 Need to use the Multipart Name for the Facet. Then use the Field @Name and Operator LIKE and enter the value in quotes. From there when you create the Policy you can select what type of objects should follow the condition.

    Reply
  • Purvesh Prajapati
    June 13, 2013 1:25 pm

    Hi Penal,

    I tried but it did not work out as you mentioned in blog in SQL SERVER 2008 R2. When i evaluate the policy manually; then it says; you violate the Policy.

    Is it something need to configure more in Policy or any Server Level?

    Please reply if you have any answer.

    Regards,
    Purvesh Prajapati

    Reply
    • Shiv Kumar Singh
      June 15, 2013 2:50 pm

      Dear Purvesh,

      when you create policy, Pls follow the following steps it will work….
      1. give the name of policy.
      2. Select condition in Check contions.
      3. select check every storeprocedure check box only for store proc only.
      4. Evaluation mode must be “On change: prevent”.
      5. when you select this you will see second option enables check box must be checked.

      that is all and it will check all store proc as per your condition. you have used in first step of this blog…

      regards,

      Shiv Kumar Singh

      Reply

Leave a Reply

Menu