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....
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 SELECT J.name AS JobName, JS.step_name AS StepName, JS.step_id AS StepNumber, JS.command FROM msdb.dbo.sysjobs J INNER JOIN msdb.dbo.sysjobsteps JS ON J.job_id = JS.job_id WHERE JS.command LIKE '%XXXXXX%' ---- Search Stored Procs, might want to loop through all databases USE XXXXX SELECT DB_NAME() AS DatabaseName, SCHEMA_NAME(O.schema_id) AS SchemaName O.name AS SPName, MO.definition AS SPDefinition FROM sys.sql_modules MO INNER JOIN sys.objects O ON MO.object_id = O.object_id WHERE 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.
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.