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 20
compdigit44Asked:
Who is Participating?
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.
0
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
0
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

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

compdigit44Author Commented:
I'm getting the following error message: "Cannot insert duplicate key row in object 'dbo.sysoperators' with unique index 'ByName'"
0
wdosanjosCommented:
It seems you are trying to update the Name to a name already on the table.  Name must be unique.
0
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
0
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

0
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
0
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.
0
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
0
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

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

The old users id remains and well as their email address
0
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

0
compdigit44Author Commented:
Samething "0" rows updated
0
wdosanjosCommented:
Are you replacing the name and email address on the script to the actual values?
0
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
0
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

0
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
0
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?
0
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
0
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

0
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.
0
wdosanjosCommented:
Please indicate the current email address value, and expected email address value after the update.
0
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
0
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

0

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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.