-1

A user has requested they be removed from an email notification sent out by our database server, but I can't find the object responsible for the email!

I know the sender, receiver, subject, body, and time of day. I know that Database Mail is sending the email, but I can't find the source job.

I've looked through SQL Server Agent Jobs, Stored Procedures, and Database Triggers in the appropriate database container, and even looked at the Visual Studio packages on that server and still can't find it. (I also did a quick survey of the other database containers, but none have any connection with the msdb.dbo.sysmail_allitems send_request_user associated to the email in question.)

Can someone offer any advice for tracking this down? Thank you.

K r
  • 17
  • 7
  • 1
    Use a tool like SQL Search? How people store who get emails sent via `sp_send_dbmail` can be quite vendor specific; you might be storing the information in a table. All this information *should* be in your documentation. – Thom A May 11 '22 at 18:46
  • 2
    Are you sure it's the SQL Server instance sending the email? If the server's DB Mail account is used other places that'll be a problem in tracking it it down... Assuming it is the SQL Server instance, do you see the email in `msdb.dbo.sysmail_allitems`? You should look across code in your entire instance, not just within a specific database. By Visual Studip packages, do you mean SSIS Packages? You can also start a trace for calls to `sp_send_dbmail` to try and narrow things down. – squillman May 11 '22 at 18:55
  • If you think it’s coming from the database (or have confirmed it in sys mail), you could certainly capture all calls to sp_send_dbmail using Extended Events. – Aaron Bertrand May 11 '22 at 19:05
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community May 12 '22 at 12:46

1 Answers1

0

This is solved.

@Larnu, your suggestion that the information could be stored in a table was key. @squillman and @AaronBertrand, I looked into using Extended Events for sp_send_dbmail, but was unable to master the tool.

After examining the dependencies of tableA (which had the same name as the email subject I was tracking down), I discovered that tableA was a parent to tableB (which had a very generic-sounding name). In turn, tableB had a trigger! The trigger contained the list of email addresses and I was able to satisfy the original request of removing specified user from all future mailings.

Thank you all for your help.

K r
  • 17
  • 7