Using Mandate Database with SQL Server Policy Based Management

By: Ken Simmons   |   Updated: 2010-08-18   |   Comments   |   Related: > Policy Based Management

Problem

There seems to be a lot of confusion around the Mandate Database option when managing categories in Policy-Based Management. In this tip I will show you how you can take advantage of the Mandate Database option to allow database owners to subscribe to only the policies applicable to thier database while still enforcing universal policies as well.

Solution

First, let's open the Manage Policy Categories dialog box shown in the following image. Expand the Management folder in SQL Server Management Studio, Right Click the Policy Management node and select Manage Categories from the context menu.

The first thing to point out here is the category. If you create a policy and do not specify a category, the policy is placed here. You should also be aware that you cannot uncheck the Manadate Database option on the category. Therefore, any policies you create without specifying a category will automatically be evaluated against all databases. I have created a new category named PCI and unchecked the Manadate Database option. This could be useful for example if you had credit card information in a couple of databases and had to enforce special policies such as encryption.

Now that I have a category that is not mandated, I can choose to subscribe to that category on a per database level. To add a database to the category, Right-Click the database, select Policies -> Categories to display the Categories dialog box displayed in the following image.

Now I can select the Subscribed checkbox so the database will be included whenever any policy in PCI category is evaluated. Since you cannot select individual policies within a category, you may want to put some extra thought into how you create your categories. If you create categories at a more granular level instead of grouping everything in the category, you may be able to take advantage of subscribing only the databases applicable for those categories.

Next Steps

• Review more tips on Policy-Based Management
• Get more tips in Pro SQL Server 2008 Policy-Based Management

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips