By: Ken Simmons | Updated: 2010-09-08 | Comments | Related: > 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
- Review more tips on Policy-Based Management
- Get more tips in Pro SQL Server 2008 Policy-Based Management
- Find out more information on the topics in Books Online
About the author
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-09-08
