Skip to main content

T-SQL Tuesday #007 - Policy Based Management

It's that time of the month again, time for the next round of T-SQL Tuesday posts. This month is being hosted by Jorge Segarra (Blog | Twitter) and we are blogging about new features in SQL Server 2008 and SQL Server 2008 R2. Plenty of scope then as this is quite a wide topic. I have posted previously on file streaming, you can see those posts here but for this post I am going to keep it simple and fresh and look at another new feature of SQL Server 2008, Policy Based Management.
I have been meaning to write something on policy based management for some time. Policy Based Management allows DBAs and administrators to apply policies or enforce rules on all the SQL Server 2008 instances they look after and ensure that a consistent approach is followed on all instances. It can be used by DBAs to enforce a 'standard' configuration across the SQL Server farm.

Where would you use it?

Well, firstly only on SQL Server 2008 and beyond. It won't work on 2005 or 2000.
There are numerous uses, one that I would have used in a previous job had it been available in earlier versions would have been to enforce some sort of naming convention. For example, you want developers to stick to some sort of naming convention, you may want the name of all stored procedures to start 'USP_' which makes identifying developer written stored procedures very easy and straight forward. Naming conventions is big long argument possibly for another day and I don't want to go into a big long discussion about this here, so let's assume you have brand new SQL Server 2008 Server, it's going to be a development instance, and you want to make sure that your developers stick to a naming convention for Stored Procedures so how do you use policy based management to achieve this?
Taken from Book Online (BOL):
Policies are created and managed by using Management Studio. The process includes the following steps:
1. Select a Policy-Based Management facet that contains the properties to be configured.
2. Define a condition that specifies the state of a management facet.
3. Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.
4. Check whether an instance of SQL Server is in compliance with the policy.

How do we do this ourselves?

Connect to your SQL Server instance
Expand the management folder
and expand the policy management folder...and you will see three further sub folders: polices, conditions, and facets.
Expand the facet folder and look for a facet called “Stored Procedure”. Right click and select <properties>. You should see a list of properties, we will be using the 'name' property. You can then close this window.
Right click the Stored Procedure facet and select <new condition> give it a meaningful name..."SPName"
The Stored Procedure Facet should be selected.
In the expressions box click to add a clause.
Enter the following information:
Field: @Name
Operator: LIKE
Value: 'USP_%'
After entering this information this your condition should be ready. Click <OK>
Using the stored procedure facet we have created a condition for the Name property which specifies that stored procedure names should start with 'USP_' we now need to create a policy to enforce this condition.
Right Click the <Policies> folder and select <New policy> and we get the Create New Policy window
Give your policy a meaningful name "SPName"
Select the check condition we just created.
Specify the target databases you want enforce the policy on.
Specify the Evaluation Mode. For this we want the policy to prevent Stored Procedures so we select "On Change: Prevent"
Click on the "Description Page" on the left hand side and enter some text in the "test to display" window something like "Sorry guys, you know the rules, nice names for the SPs please."
Click OK. This creates your policy. BUT you are not done there. If you go and try and create an SP that does not follow this policy do you think SQL Server will allow you? Well yes it will, the new policy is currently in a disabled state. I think this is like this because they want you to evaluate your policy before you enable them and you can right-click and run evaluate which shows you current objects which break this policy.
So I will enable the policy by right clicking and selecting <Properties> and the ticking the <Enabled> check box.
go away and a developer tries to create an SP called '[SELECT]' and he gets this
When we change the name to ‘usp_select’ it works just fine.
That just about wraps up my TSQL Tuesday post for this month, my good friend Justin Hostettler-Davies has recently been writing a series all about the new features of SQL Server 2008. You can find his blog and his posts here.


  1. Great job but just for the record you CAN use PBM with 2000 and 2005. Check out . PowerShell extends the power of PBM to the rest of your environment.


Post a Comment

Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

New in SQL Server 2017: Graph Databases

David has recorded and published a video of his presentation on SQL Server Graph Database. In his video which you can watch below, David provides an excellent introduction into SQL Server 2017 Graph Databases. In his presentation he looks at Tennis results at tournaments for  his favourite player "The Fed"  Rodger Federer.

David  shows how to set up graph database and work with them in SQL Server 2017.

Graph Database is not new. Other vendors have had graph database capabilities for some time so Microsoft are quite late to the market. In David presentation it appears that Microsoft have done a reasonable job of implementing some of the graph database features but he does point some of the limitations of the Microsoft product too and suggests that it is not ready for production yet but Microsoft seem serious about this feature.

Please watch the video and feel free to leave a comment or feedback - David is delivering a version of this talk on Graph databases in SQL Saturday Ka…