One of the most useful things I have found with Policy Based Management is the ability enforce my corporate naming standards. There are many reasons to have these standards, but I think supportability reigns supreme. For more on my thoughts about naming standards you can read my post SQL Nomenclature.
Today let’s look at how to enforce the naming convention for stored procedures. For our example we have two criteria for the naming of stored procedures. The first is that we do NOT want it start with “sp_” since that is the system naming convention. The second is that we want it start with “usp_”. We can meet both of these criteria with a single expression in our policy condition. Our first step is to create our condition by right clicking conditions under policy management and selecting “New Condition”. Here we will give it a useful name, use the stored procedure facet, and define an expression to evaluate based on the @Name field. This is how it should look.
Next we want to create our policy definition by right clicking policies under policy based management and selecting “New Policy”. Here we will give the policy a useful name, select the condition we created in the previous step, accept the default targets, select “On Change: Prevent” for the evaluation mode, and enable the policy. I want to point out two things here before I show you what it looks like. The first is that you cannot enable the policy until you have selected “On Change: Prevent” for the evaluation mode. “On Demand” is the default evaluation mode and policies cannot be enabled in that mode. I’ll elaborate more on that in a future post. The second thing to point out is that we are sticking with the default targets which are “Every Stored Procedure” in “Every Database”. The “Every Database” target is a default target that should really be named “Every User Database”, because it does not include your system DBs like Master, Model, TempDB, and MSDB. For more information on how to get it to apply to user and system databases, please come back next week and see my post on Why My Policy will not Evaluate System DBs. Here is what the final policy definition looks like.
The last thing we need to do is fill out the description tab. First we will create a new category called “All Naming Conventions” because we know we will have other naming convention policies for other database objects. Next we will define the description, text to display, and address fields. These fields will be displayed to the users who violate this policy. The information on this tab is optional, but I highly recommend filling it out so users who violate the policy will understand why, how to conform to the policy, and where to find additional help. Here is what our description tab will look like when completed.
We have now completed the creation of a stored procedure naming convention policy that will prevent the creation of any SPs that do not follow the policy. The policy will also provide the user with any pertinent information on how to comply with the policy. The last thing we need to do is attempt to create a policy that violates this naming convention to ensure the expected policy behavior. Here is what we should see.
