I merely ready about policy based management (PBM) through short snippets here and there, but never really got that in-depth with it as I normally do with other subjects. I think the major factor was due to the name Policy. Mistakenly I automatically associated it with Active Directory. PBM was introduced with SQL Server 2008 and offers great benefits with administering SQL Server. I was conducting some research on the subject and found a post by Ashish Kumar Mehta titled: “Identify Databases Not in Full Recovery Model Using Policy Based Management” which steps you through the process of creating a simple policy and runs you through the evaluation process. Ashish covers the steps rather well so be sure to visit the post for more details. If you want to get deeper into the terminology, scheduling and such then visit a post by Ray Barley titled: “Using Policy-Based Management in SQL Server 2008”
After walking through Ashish’s post I wanted to make one similar but I wanted to return all Windows Accounts and such, though his use for the Recovery Model is more practical. So fire up your SSMS 2008 and let’s get started. This is just a quick run down of how to create one. This does not get into the deep details that others like have. More of a pre-primer primer if you will.
Open SQL Server Management Studio and double-click or expand Management.
Right click on Policies
Select New Policy…
Give your policy a name
Click the Check Condition and Select New Condition…
Give your Condition a name… I know it requires a lot of naming
Select the Facet drop down and Select Login
In the Expression section click on the Field column and select @LoginType
Select the Operator (=)
Select WindowsUser for the Value and hit Enter
Click Ok to create the Condition and the Policy
Policies should be expanded now and you should see your newly created policy. Expand Condition and you should see the condition you just finalized.
Now let’s test the Policy. Right click on the newly created Policy and select Evaluate.
And this brings us to the end results
The green marks are the Windows Accounts and the Red marked rows are not.
In the event you are working on a server that may or may not have policies in place Ken Simmons provided me with the following select statement that will return information about all the existing policies.
SELECT * FROM msdb.dbo.syspolicy_policies
In the event you incorrectly spelled a policy here is a method that will allow you to rename it. Again thanks to Ken Simmons with the #sqlhelp tweet.
SELECT * FROM msdb.dbo.sp_syspolicy_rename_policy @name = 'OldName', @new_name = 'NewName
Ken Simmons (website | @KenSimmons) is also a published author on this very subject so check out his book titled: (Pro SQL Server 2008 Policy-Based Management) with was co-authored by Jorge Segarra (website | @SQLChicken) and Colin Stasiuk (website | @benchmarkIT)
ayesamson 