Edit

Manage Policy Categories

  • Article

Applies to: SQL Server

This topic describes how to apply any or all available policies in a category to the whole instance of SQL Server by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before You Begin

Limitations and Restrictions

  • When using sSQL Server, 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).

Security

Permissions

This stored procedure runs in the context of the current owner of the stored procedure.

Using SQL Server Management Studio

To apply category policies to a SQL Server instance

  1. In Object Explorer, click the plus sign to expand the server where you will apply category policies.

  2. Click the plus sign to expand the Management folder.

  3. Right-click Policy Management and select Manage Categories.

    The following information is available in the Manage Policy Categories dialog box:

    Name
    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.

  4. Select or clear any or all check boxes under Mandate Database Subscriptions to apply that policy category to the SQL Server instance.

  5. When finished, click OK.

Using Transact-SQL

To apply category policies to a SQL Server instance

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. 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'AdventureWorks2022'  
, @policy_category = N'Table Naming Policies';  
GO

For more information, see sp_syspolicy_add_policy_category_subscription (Transact-SQL).