Preventing Undesired Actions Using Policies.
Applies: SQL Server 2008 (Katmai) CTP November 2007
Microsoft SQL Server 2008 introduces Declarative Management Framework, which can
be used to prevent changes to the system that don't comply with standards in
Let's say we would like to implement a coding standard that all tables
created from now on for a specific database should begin with the prefix 'tbl'.
Now, let's create a policy to enforce that and let's use AdventureWorks database
Let's open SQL Server Management Studio, expand Management, then expand
Policy Management, right-click Condition, and then click New Condition.
In the Create New Condition dialog box, in the Name box, type DBA - Table Name
Prefix. After typing the name, select the facet Table, then choose the
expression @Name like 'tbl%', and click OK
Remember that for testing purposes we would like to apply our new policy to the
AdventureWorks database. For that reason, we need to create a new
condition specifying just the AdventureWorks database, and let's name it 'DBA -
We can now create our policy. In Object Explorer, right-click Policies,
and then click New Policy.
In the Create New Policy dialog box, in the Name box, type DBA - Table Name
Prefix Policy. Then select the condition 'DBA - Table Name Prefix'.
Specify as target every table and select the 'DBA - AdventureWorksDB' condition
for database target.
The last step for creating our policy is specifying 'On Change - Prevent' as
execution mode, since we want the policy to prevent the creation of every table
with a prefix different than 'tbl'. After that, please click OK.
Let's test our policy, creating a new table that does not comply with the
standard expressed in the policy.
Let's create a column for the table and quickly try to give a table name like
'TestTable', which does not have the 'tbl' prefix.
Here is when the policy comes to action, and prevent the creation of the
table since it does not comply with the policy.
As you can see, this should bring peace of mind to many DBAs around the world,
including me ... Welcome SQL Server 2008!