SQL server Email change

Question

Hi , My organization is migrating its email from abc domain to another xyz domain.
So Database team task is to identify all the email operators, db mail operators and change the domains for around 250 SQL server which is in our inventory.

Also, I need to find all the objects( stored procedures etc) where this domain is getting used. So i need to find the query which will give these details. What should be the strategy and planning for this.

Can anyone help with the query and the process we should follow to implement this.

Answer 1

I ran this query in msdb:

SELECT object_name(object_id), name
FROM   sys.columns
WHERE name LIKE '%mail%'

It seems that there are columns in msdb you need to check.

To find references in stored procedures etc in a database, you can run:

SELECT object_name(object_id) 
FROM   sys.sql_modules
WHERE definition LIKE '%abc.com%'

Then the challenge is to run this on all your 250 servers, and on all databases (but the system databases) on every server. If you have not been using Powershell in your daily work, here is an excellent chance to get started!

Answer 2

Hey Thanks for the suggestion.

Answer 3

Hi ChandraKumari-3622,
In addition, here are some considerations about changing domain for SQL Server which might help.
Best Regards,
Amelia

Answer 4

And you need to modify from the mail related tables.

use msdb
go
select * from sysmail_account

go
select * from sysoperators

You can use sysmail_update_account_sp and sp_update_operator to update email address from in sysmail_account and sysoperators tables.