Manage Policy Categories
Applies To: SQL Server 2016
This topic describes how to apply any or all available policies in a category to the whole instance of SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To apply category policies to a SQL Server instance, using:
When using SQL Server 2016, if the Mandate Database Subscriptions check box is not selected, the policy category must be individually applied to each relevant portion of the server, such as one or more databases or tables.
If you specify a policy category that does not exist, a new policy category is created and the subscription is mandated for all databases when you execute the stored procedure. If you then clear the mandated subscription for the new category, the subscription will only apply for the database that you specified as the target_object. For more information about how to change a mandated subscription setting, see sp_syspolicy_update_policy_category (Transact-SQL).
This stored procedure runs in the context of the current owner of the stored procedure.
In Object Explorer, click the plus sign to expand the server where you will apply category policies.
Click the plus sign to expand the Management folder.
Right-click Policy Management and select Manage Categories.
The following information is available in the Manage Policy Categories dialog box:
The name of the policy category.
Mandate Database Subscriptions
Forces all databases on the instance of SQL Server to enforce policies in the policy category.
Select or clear any or all check boxes under Mandate Database Subscriptions to apply that policy category to the SQL Server instance.
When finished, click OK.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE msdb; GO -- configures the specified database to subscribe to a policy category that is named 'Table Naming Policies'. EXEC dbo.sp_syspolicy_add_policy_category_subscription @target_type = N'DATABASE' , @target_object = N'AdventureWorks2012' , @policy_category = N'Table Naming Policies'; GO
For more information, see sp_syspolicy_add_policy_category_subscription (Transact-SQL).