Renaming SQL Server Policy Based Management Policies


By:   |   Updated: 2010-09-08   |   Comments   |   Related: More > Policy Based Management

Problem

I have created several policies using Policy-Based Management and need to rename some of them. I don't see an option in the GUI to rename a Policy. Is there any way I can rename them without dropping and recreating them?

Solution

When you right-click on a Policy, you don't get a rename option like you do with many other objects in SQL Server. In addition, when you open a Policy, the name textbox is disabled and cannot be changed. Luckily, you can use the stored procedure sp_syspolicy_rename_policy located in msdb to rename a Policy.

Here is the syntax for this command.

sp_syspolicy_rename_policy 
{ [ @name = ] 'name' | [ @policy_id= ] policy_id }, 
[ @new_name = ] 'new_name'

You can choose to supply either the policy_id or the current policy name to identify the policy and simply provide @new_name parameter with the new Policy name.

Since we don't all go around memorizing our policy_id's, if you would rather use the id instead of the name, you can find it in the syspolicy_policies view in msdb. Querying syspolicy_policies may be useful if you are using a scripted approach. For example, if you needed to rename all policies to start with a certain prefix. However, in order to rename a single Policy named ChekBaaakups, all you have to do is execute the following command.

sp_syspolicy_rename_policy @name = 'ChekBaaakups', 
@new_name = 'CheckBackups'

You can also rename Conditions with a procedure called sp_syspolicy_rename_condition using the same syntax, except the procedure accepts the Condition name and condition_id. You can find all the information on the Conditions using the syspolicy_conditions view in the msdb.conditions view in msdb.

Since the condition_id remains the same, the Condition is still tied to all of the Policies and the name change is reflected there as well. In other words, your Policies will not break because they are looking for a Condition that no longer exists or you will not get an error message saying that you can't change the name of a Condition because it is currently being used.

Next Steps


Last Updated: 2010-09-08


get scripts

next tip button



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





Comments For This Article





download


Recommended Reading

Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance

Using Policy Based Management in SQL Server

Internal SQL Server Certificates have expired is this an issue

Enforce SQL Server Database Naming Conventions Using Policy Based Management

Configuring Alerts for SQL Server Policy Based Management





get free sql tips
agree to terms


Learn more about SQL Server tools