Most of company has their own policies related to the business in which they deal and to implement those policies on production server is very tedious work. SQL Server 2008 introduces new feature of policy based management to implement business policies and security policies on production.
Policy based management is collection of various policy objects so we use to call it as Management Framework. This framework allows us to configure various policy related to particular objects and then any violation done against policy will prevent by automatically or manually, changes will rollback. Management is very easy using SSMS (SQL Server Management Studio), same can be done by T-SQL command.
Once we setup the policies by using SQL Server Management Studio it is easy to manage also monitors the changes done on production.
Let’s say we want to setup a policy for developer so that system should not allow them to use stored procedure name start with ‘sp’ which indicate system procedure.
We will use SQL Server Management Studio to create policy first on testing server before implementing same on production. It is good practice to implement the change on test environment to know the impact. Follow the below steps to create policy in SQL Server 2008.
1. Connect to test server by user name and password
2. Click on Management –> Policy management / policies
3. Right click on policies –> choose New Policy option from the context
4. It will open New policy window, give the Policy name as “testpolicy”.
5. In Check Condition option select predefined condition or click on new condition option to create. Condition is nothing but restriction wants to put on developer, without condition policy is invalid it will not allow us to create policy. After clicking on new condition option it will open new dialog box. Another way to create condition is right click on Condition folder –> new condition
6. Give the name as “testCondition” and select facets from the drop down list
• Select facets as “Store Procedure” and select “name” option from the fields under expression.
• Select operator as NOT LIKE and value ‘sp%’ as shown below.
What is facet ?– Its basic unit of this framework which includes objects like Surface Area Feature, logins, server, database, users and so on. Its set of predefined properties which plays important role while setting condition for specific object. SQL Server 2008 has total 47 facets with 1492 properties.
We can check out by clicking on facets folder under policy Management. Now click on OK to continue
7. It will come back to earlier window of new policy, here select the evolution mode as “On Change : Prevent”
We have 4 options available in Evaluation mode.
• On Demand – After setting this option we have to execute this manually to check the discrepancy, by default it is disable.
• On Schedule – We can schedule this to check the policy on specific date and time.
• On Change: log only – Check the policy whenever change occurs and logs the change done if any policy violated by user
• On Change: Prevent – Check the policy whenever change occur and if any policy violated, prevent the change.
We are chosen On Change: prevent option from the drop down list.
We are using local server for our policy setup for testing so we left server restriction option as none.
Click on OK to finish the setup.
Now we have created the policy to restrict developer for creating store procedure which starts with ‘sp’. Lets check whether we are able to create store procedure of named sp_proc1 in testdb or not.
CREATE PROCEDURE sp_prc1
After executing above we got below error message.
Policy ‘testpolicy’ has been violated by ‘SQLSERVER:\SQL\ABRL-NUNQO38SVH\SQL2008\Databases\master\StoredProcedures\dbo.sp_prc1’.
This transaction will be rolled back. Policy condition: ‘@Name NOT LIKE ‘sp%”
Policy description: ”
Additional help: ” : ”
Statement: ‘create proc sp_prc1
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 The transaction ended in the trigger. The batch has been aborted.
We also executed below query to confirm the same. No result changes are reverted to back.
select * from sys.sysobjects where type = ‘p’ and name like ‘sp_proc1’
It is easy to implement and manage by SSMS if you want to change the condition than double click on condition name, it will open properties window change it accordingly and test.
In next article we will see how to create policy for specific object and applying of same on multiple servers.