We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via the email operator as setup on the associated server. The email is definitely sent from the SQL server in question as when I query the msdb in the sysemail_mailitems the email the recipients received definitely exists as shown in the screenshot below. All we want to do is change the recipients for the notification but only if we knew where....

enter image description here

2 Answers 2


It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.

---- Search Job Steps
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,

msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps JS
ON J.job_id = JS.job_id

JS.command LIKE '%XXXXXX%'

---- Search Stored Procs, might want to loop through all databases 


DB_NAME() AS DatabaseName,
SCHEMA_NAME(O.schema_id) AS SchemaName
O.name AS SPName,
MO.definition AS SPDefinition

sys.sql_modules MO
INNER JOIN sys.objects O
ON MO.object_id = O.object_id

MO.definition LIKE '%XXXXXX%'

Replace the WHERE clause with a short bit from the email itself that you have confidence would be in the body of the definition. It's not obvious from your post if some of that content in the email is dynamic or the same every time.

  • Thank you the first query gave it away. I inherited this system from someone else and they have stored procedures all over the place. The job called on a stored procedure which had the code for the email notifications. Thanks a lot.
    – domdew
    Commented Oct 15, 2020 at 14:14

In addition to BCM's answer:

The subject of the mail is an indicator if it comes from a job. Just compare it to an email that you know comes from a scheduled job.

Also, if it isn't Agent, then it it can be a module (stored procedure, trigger).

But it can also be SQL (sp_send_dbmail) submitted from a client. In that case, you would have to try to capture it through a trace. OTOH, since it is initiated outside SQL Server, you would have to change the recipient outside SQL Server as well.

  • Thanks for the tip.
    – domdew
    Commented Oct 15, 2020 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.