Renaming SQL Server Agent operator

Question

I want to rename SQL Server Agent operator, that is change it's name.

Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do anything. However, clicking on selected operator in Object Explorer also allows you to rename it and produces desired result.

My other concern is existing jobs that were setup to send notifications using old operator's name. I wouldn't want them to fail to send notifications but on the other hand would like to avoid changing all of them manually. I dug around a bit and found that table dbo.sysjobs in msdb uses operator ID, so it seems it shouldn't be a problem. I also tried it on a test job and after renaming operator everything still worked as expected plus job properties displayed new operator name without me having to do anything. So it looks like it should not cause any problems at all but I would like some confirmation on this.

Answer 1

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.