mssqltips logo

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools