Policy In SQL Server

SQL Server 2008 +

Policy based management introduced in sql server 2008 is really a nice feature. Where DBA can manage certain activity by enforcing policy based on specified conditions. You can enforce policy at database level.

How To Create Policy ?

The following step by step shows creation of policy for a stored procedure name standard.

Create a condition for policy

Create A Condition Expression

Facet : Stored Procedure, Field = @Name and Value=’Usp_%’, It Means that any stored procedure name must start with “Usp_” If it does not starts with Usp_ then user won’t be able to create that stored procedure.

Now Condition has been created, Next step is to create policy on condition create above.

How To Create Policy ?

Define Policy

Here "Pol_Create_Stored_Procedure”  has been defined with condition we created above which will be targeting all the databases and evaluation mode has been set to On Change prevent. these evaluation mode depends on facet we define in condition. Following is the list of evaluation mode.

On demand - The policy is evaluated only when directly ran by the administrator.
On change: prevent - DDL triggers are used to prevent policy violations.
On change: log only - Event notifications are used to check a policy when a change is made.
On schedule - A SQL Agent job is used to periodically check policies for violations.

Now Lets Try to Create a Stored Procedure

You can see the above screen shot where I tried to create stored procedure “Sp_GetData” which doesn’t pass the policy and resulted in batch aborted.

Now Let Try to use Usp_

 

So you saw that you can enforce policy to following standard naming conventions. There are many facets on which condition and policy can be defined.