Database Simplified Headline Animator

Database Simplified

Saturday 29 October 2011

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.


  1. The Casino Site 2021
    Welcome to the BEST Online Casino Site in the World! Our casino choegocasino site offers a huge variety of casino games to choose 인카지노 from! Signup and start playing Payout Limit: $30Deposit Methods: BitcoinMasterCard, Paypal, 바카라사이트 Paypal, Stays: Within 24 hours,