Policy Based Management in SQL Server

Policy management enables us to implement the policies in SQL Server. Suppose we have implement policy in a user database for each procedure should not start with sp_

Then we have to implement the policy using the following steps.

In order to create policy we have to create a condition.

Conditions will use facets to write a condition using expressions.

Facets is contains objects and their property to create a condition.

Policy is used for evaluating the condition against target. 

Target is an object in which the policy can executed.

Target can be a table, database, procedure etc.

you can see the polocies in the system from the below path

    drive\ProgramFiles\MicrosoftSQLServer\100\Tools\Policies\Database Engine\1033

A policy can be evaluated in different ways

•          On schedule

•          On Demand

•          On Change Prevent

•          On change Log Only

I will show here how to create policy using SQL Server Management Studio

First We have to create condition in order to create policy.

Right click on conditions from Management --> Policy Management --> Conditions

We are creating policy for store procedures that user cannot create procedure with name sp_

@Name means procedures and operator we are using not like sp_

Then we will create policy in order to evaluate the condition for stored procedures

Provide the policy name and select the condition from the drop down. 

We can select the evaluation mode as shown in the above picture. for executing the policy in only specified database we can create one more condition for the target. 

We can provide the filter using database facet with @name

Now the condition is applicable to DBmirror Database.

We have to select evaluation mode as on change prevent in order to avoid creating procedures with name sp_ .We enable this policy as shown below.

Then we will try to create a new procedure with the name sp_

The policy has been evaluated as shown below.

We can fetch the condition from the below query

select condition_id,name,created_by,facet,expression

from msdb.dbo.syspolicy_conditions

Similarly we can find the policies using the following query

select policy_id,name,condition_id,schedule_uid

from msdb.dbo.syspolicy_policies

select*from msdb.dbo.syspolicy_policy_execution_history

select*from msdb.dbo.syspolicy_policy_execution_history_details

select*from msdb.dbo.syspolicy_system_health_state

select*from Msdb.dbo.syspolicy_policy_category_subscriptions

For deleting the policy

EXEC msdb.dbo.sp_syspolicy_delete_policy@policy_id=1

GO

EXEC msdb.dbo.sp_syspolicy_delete_object_set@object_set_id=1

GO

For deleting the conditions we have use the below system stored procedure

EXEC msdb.dbo.sp_syspolicy_delete_condition@condition_id=1