March 11, 2024 at 3:45 pm
Hi, folks, I've inherited a couple of 2016 SQL servers, and they both have maintenance plans on them with a handful of sub-plans. Each sub-plan has a pair of "Notify Operator Task" tasks, 1 each for success and failure. The "View T-SQL" button on these tasks indicates that it's running the msdb.dbo.sp_notify_operator system stored proc.
What has me stumped is where this is pulling the from email address. The destination emails are correctly selected and sent to the chosen Agent operators just fine.
I thought maybe it was using the Agent Alert System mail profile as the send-from address, but that is not the case: the alert system profile is mapped to a different email address than the one that the Notify Operator Task is sending from.
In fact, the "from" email address for these notification emails does match to an account set up in DB Mail, but there is no DB Mail profile that maps to that account. I don't know if the maintenance plan cares about DB Mail accounts or not -- it seems to use Agent operators, not DB Mail accounts.
I've looked at BOL and elsewhere and haven't found any information about this. Any thoughts?
Thank you!
Rich
March 11, 2024 at 4:03 pm
Hi, folks, I'm replying to my own post with an answer, in case anyone is interested:
From this post (https://dba.stackexchange.com/a/220808), this query will return which is the default profile on the DB Mail system:
SELECT *
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id;
That was key for me, because it pointed me to a profile that was named using one email address, but in fact was mapped to an account with a different email address. Thus a two-part confusion as to how to identify where the emails were coming from.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply