Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Mandate Database with SQL Server Policy Based Management


By:   |   Read Comments   |   Related Tips: More > 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.

Expand the Management folder in SQL Server Management Studio, Right Click the Policy Management node

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.

 Right-Click the database, select Policies -> Categories to display the Categories dialog box

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


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools