In this step by step SQL Tutorial I will show you how to create new policy. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- Policy based management does not exist in SQL Server 2005
Who is it for?
- People who would like to learn basics of creating new policy
- Those who are preparing for Microsoft certifications and need to cover this topic.
Why to create new policy
In one of my previous blog post I explained what is SQL Sever policy based management and I also used an example of issue in SQL Server when cast date and cast datetime is used with certain login language and in this blog post I will show you how to create new policy that will ensure that each login in my SQL Server 2012 instance has Login Language set to English.
Before I create a new policy I need to a condition that checked for Login Language and you can check how to create new policy condition article for more details.
Let's start our step by step tutorial.
First I go to Policies folder (see below), right click the folder and select New Policy...
Next I fill in details. I will call my policy LoginLanguage, I selected condition 'CheckLoginLanguage' that I created earlier (and provided link above). My against targets is set to Every Login and evaluation mode is set to On Demand.
NOTE: You can change On Demand to On Schedule but Login facet does not have certain options like run automatically (when new login is created/modified). This option is available for some other facets (usually database level specific like stored procedure facet).
You can see that new policy has been created
Policies have option evaluate (after you right click the policy)
and evaluate will give us information about logins in this case that do not comply with our policy.
I have created a login with Language British and after I click 'view' I can see that login does not comply with policy that tells me that each login should be set to English
In my case I will select all logins and apply policy and that means the policy will change language of Login to 'English' which is what I specified in condition.
I get a warning and click ok
and can see that all login now comply with policy.
Below I manually check login and I can see language has been changed from British to English.
I hope you find this simple article useful.