Create the Finance Name Policy
Applies To: SQL Server 2016
In this task, you will create a database named Finance, and then create a condition that requires all tables to start with the letters fintbl. Then, you will create a policy and policy category to enforce a naming standard for tables in the Finance database.
In Management Studio, open a query window and execute the following statement:
CREATE DATABASE Finance ; GO
In Object Explorer, click Databases, and then press F5 to refresh the list of databases.
In Object Explorer, expand Management, expand Policy Management, right-click Conditions, and then click New Condition.
In the Create New Condition dialog box, in the Name box, type Finance Tables.
In the Facet list, select Multipart Name.
In the Expression area, in the Field box, select @Name; in the Operator box, select Like; and in the Value box, type 'fintbl%' to force all table names to start with the letters fintbl.
On the Description page, type Finance table names must begin with fintbl, and then click OK to create the condition.
In Object Explorer, right-click Policies, and then click New Policy.
In the Create New Policy dialog box, in the Name box, type Finance Name.
In the Check condition list, select Finance Tables. This is in the Multipart Name area.
In the Against area you will see a list of the database objects that could apply this policy. Select the check box for Every Table.
In the Every Database area, expand Every, and then click New condition.
In the Create New Condition dialog box, in the Name box, type Finance Database.
In the Expression box, complete the expression to include @Name = 'Finance', and then click OK to close the condition page.
You might have to tab out of the Value box to enable the OK button.
In the Evaluation Mode list, select On change: prevent. This will enforce the policy by creating a database trigger on the Finance database.
Select the Enabled list. (The Enabled box does not apply to On demand policies.)
In the Server restriction list, select None.
In Object Explorer, expand Management, right-click Policy Management, and then click Manage Categories.
In the Manage Policy Categories dialog box, under Name, type Finance in the blank box, and then clear Mandate Database Subscriptions. Mandate Database Subscriptions will force every database in the instance to subscribe to the policies that belong to this policy category. For this lesson, only the Finance database should subscribe to the Finance Name policy.