Defining SQL Server 2008 Policies

A new SQL Server 2008 feature that allows the Database Administrator the ability to define and enforce policies through the database engine. In today’s article I’ll look at how you can use SQL Server Management Studio to define your own policies.

Define your PoliciesThe most challenging part of creating an effective database policy system is deciding what exactly it is your want to create policies for. SQL Server 2008 provides a large range of Facets (objects) for which conditions and policies can be defined for, so it will absolutely be worth the effort to take some time to map out what Policies you want to enforce.

To define a new Policy, open SQL Server Management Studio and navigate to the Management node in Object Explorer. Before I can define a Policy, I’ll first need to define a new Condition and can easily do so by right-clicking on the Conditions folder under the Policy Management folder.



A Condition is a set of criteria defined on a Facet. A Facet is really nothing more than a SQL Server object that you can involve in a Policy. In the Create New Condition screen, I define a new Condition named NewStoredProcedureNames. I can define the criteria for my new Condition in the Expressions section. Each Facet (Stored Procedure in this case) has a set of Fields for which condition expressions can be defined. For this particular Condition, I want to set criteria so that any new Stored Procedure name begins with usp_, and this is fairly straightforward to do through the editor.



Now that I have my Condition defined, I can create a new Policy.

Right click the Policy folder and select New Policy. In the Open Policy window, choose the NewProcedureNames check condition we just created. Choose the On change: prevent Evaluation Mode. This mode will evaluate the Policy when a new stored procedure is created, and if the procedure does not start with usp_, an error will be thrown and the new procedure will be disallowed. Be sure to click the Enabled box to enable the Policy.



To test my new Policy, I write a script to create a new stored procedure named GetCurrentDate that returns the current date. When I attempt to execute the script, I receive an error message letting me know that I have violated a Policy. For a friendlier message, you can define informative descriptions with your Policies so that the user is given more instruction as to what condition was violated.




Here is the text of the procedure I attempted to create above.

CREATE PROCEDURE GetCurrentDateASSELECT CAST(GETDATE() AS DATE)ConclusionToday I defined a simple Policy to prevent the creation of any new stored procedure that does not begin with usp_. The great thing about Policy-Based management is how complex you can define your Policies to adhere to your defined database policies. The more you play around with defining policies, the more creative and effective you’ll become at defining your own polices, so take advantage as soon as you can!