Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Renaming SQL Server Policy Based Management Policies


By:   |   Read Comments   |   Related Tips: 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 Update:






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





More SQL Server Solutions











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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools