Script to Remove Email Address of Notification User

I'm not a DB and have to fill in for our DB who just left. Any way we have multiple test and production SQL 2005 - 2008 servers and a total of 100 jobs with some server having 10+ notification users each. Is there a way for me to search a sql server for the user Notification name or part of the name the remove it and it associated email address and replace it with a new name and email address
LVL 21
compdigit44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wdosanjosCommented:
Check the msdb.dbo.sysoperators table.  It holds the Operators information.  You should be able to execute an UPDATE against it to change the info you need.
compdigit44Author Commented:
OK , here is the problem though I'm not a DB and not good with t-sql what would I need to type in in order to do what i listed above in my main question
wdosanjosCommented:
First check the contents of the table (below) and make sure it the record you are looking for is there.
select * from msdb.dbo.sysoperators

Open in new window

Then you can update the record as follows: (Try it out on a non-production DB first to make sure it works as you expect)
update msdb.dbo.sysoperators
set name = '<< New operator name >>', email_address = '<< new email address >>'
where email_address = '<< old email address >>'

Open in new window

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

compdigit44Author Commented:
I'm getting the following error message: "Cannot insert duplicate key row in object 'dbo.sysoperators' with unique index 'ByName'"
wdosanjosCommented:
It seems you are trying to update the Name to a name already on the table.  Name must be unique.
compdigit44Author Commented:
now can the script be build to replace the the email address of <old email person's name> with new one ..

For email user and reference to John Does <jdoe@company> would be updated to "SQL Notify Name" <sqldlgroup@company.com> regardless is reference to the new name and email already exist
wdosanjosCommented:
No, if "SQL Notify Name" is already there the only option is to delete the "John Does" entry.

Here is a better way to do that.  It uses a couple system stored procs.
USE [msdb]
GO
-- Update 'John Does' to 'SQL Notify Name'.
--    If 'SQL Notify Name' is already an operator, then 'John Does' is just deleted.
IF  EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = 'SQL Notify Name')
    EXEC msdb.dbo.sp_delete_operator @name='John Does'
ELSE
    EXEC msdb.dbo.sp_update_operator @name='John Does', @new_name='SQL Notify Name', @email_address='sqldlgroup@company.com'
GO

Open in new window

compdigit44Author Commented:
Here is another problem. The old users name is tied to operators with multiple names for example of our operators are listed as..

User1, users 2 etc..

How can this scrupt search of any reference to the old users name and change it to whatever
wdosanjosCommented:
Yes, a script can handle that.  But in order to craft the script correctly I would need some sample data with the current Name / Email and the corresponding new Name / Email address.

Something like this:

Current Name/Email: User 1 / user1@company.com
New Name/Email: SQL Notify Name 1 / sqldlgroup@company.com

Current Name/Email: User 2 / user2@company.com
New Name/Email: SQL Notify Name 2 / sqldlgroup@company.com

etc.
compdigit44Author Commented:
Does this help....

Current Name/Email: John Doe / jdow@company.com
New Name/Email: SQLDLGroup/ sqldlgroup@company.com

I want to change *all* references to John Doe to be SQLDLGroup name and email address
wdosanjosCommented:
Hopefully this will work for you.  It searches the old user by the email address, then updates the name/email address handling duplicates with a sequence on the name, for example: SQL DL Group (1), SQL DL Group (2), etc.
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where email_address = 'jdow@company.com'
     order by id
  
update [msdb].[dbo].[sysoperators]
   set name = 'SQL DL Group (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'sqldlgroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

compdigit44Author Commented:
Zero row's were updated...

The old users id remains and well as their email address
wdosanjosCommented:
It's hard for me to provide the correct script without seen the actual data.  Let's try again.  This version searches the user by name:
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('John Doe%')
     order by id
  
update [msdb].[dbo].[sysoperators]
   set name = 'SQL DL Group (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'sqldlgroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

compdigit44Author Commented:
Samething "0" rows updated
wdosanjosCommented:
Are you replacing the name and email address on the script to the actual values?
compdigit44Author Commented:
yes..below is what I'm basically doing..



declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('Joe1 Doe1%')
     order by id
 
update [msdb].[dbo].[sysoperators]
   set name = 'SQLGroup (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'SQLGroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id
wdosanjosCommented:
OK. I see.  Please try this:
declare @op table (seq int identity, id int)

insert into @op (id)
    select id
      from [msdb].[dbo].[sysoperators]
     where lower(name) like lower('Joe%Doe%')
     order by id
 
update [msdb].[dbo].[sysoperators]
   set name = 'SQLGroup (' + cast(op2.seq as varchar(10)) + ')',
       email_address = 'SQLGroup@company.com'
  from [msdb].[dbo].[sysoperators] op1
 inner join @op op2 on op2.id = op1.id

Open in new window

compdigit44Author Commented:
I had to change this line: where lower(name) like lower('Joe%Doe%')

to

where lower(name) like lower('%Joe%Doe%')

We are really close the script ran and update the fields but it over wrote the operator name instead of finding and replacing
wdosanjosCommented:
Regarding "but it over wrote the operator name instead of finding and replacing", please provide more details.  What it updated to? And, what was the expected result?
compdigit44Author Commented:
For exmample i had an operator called:

User1, Joe Doe

After I ran the script which was set to look for Joe Doe's ID

the operator now was replaced with the new SQL DL group name

SQLGroupName

The goal was to be:

User1, SQLGroupName
wdosanjosCommented:
OK. I think I have a better understanding of the problem now.  Let's try one more time:
update [msdb].[dbo].[sysoperators]
   set name = rtrim(replace(replace(name, 'Joe', 'SQLGroup'), 'Doe', '')),
       email_address = 'sqldlgroup@company.com'
  where lower(name) like lower('%Joe%Doe%')

Open in new window

compdigit44Author Commented:
This is almost perfect... The operator display changes or appends the old users name with the new DL group but it does append the email address. It overwrites it with the DL group.
wdosanjosCommented:
Please indicate the current email address value, and expected email address value after the update.
compdigit44Author Commented:
For Example..

Operator ID = user1,Joe Doe
email address = user1@copmany.com, JDoe@company.com

The current script set the email address to look like the following:
email address = sgql@company.com

Intended outcome should be: user1@comany.com,sqldl@company.com
wdosanjosCommented:
OK. Please try:
update [msdb].[dbo].[sysoperators]
   set name = rtrim(replace(replace(name, 'Joe', 'SQLGroup'), 'Doe', '')),
       email_address = replace(email_address, 'JDoe@company.com', 'sqldlgroup@company.com')
  where lower(name) like lower('%Joe%Doe%')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compdigit44Author Commented:
It seams to be working perfectly!!!

Thank yo so very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.