I'm writing a utility procedure that is runnable by ImpUsers (as a report), and it needs to send database mail (in real time). Database Mail has been covered in previous TLCC "Advanced SQL" sessions, but it does not appear possible to call sp_send_dbmail directly from a stored procedure that will be run by someone in the ImpUsers role, without first granting those users DatabaseMailUserRole membership in the msdb database.
I am wondering what best practices are for this, and how others have solved the problem (call out to Wayne Evans who does this in the Reset Passwords shared report and Kim Lee who presented a variation at TLCC last year).
My present course of research is going to be creating a wrapper procedure for the dbmail call using "CREATE PROCEDURE... WITH EXECUTE AS OWNER" as a way to elevate the execution context of the utility.
Hi Nick,
I followed the same approach as what you're thinking of. My wrapper procedure is kind of a souped-up version of AP_SEND_SMTP_MAIL, that allows emails to be sent in HTML or plain text, etc, but it also has the "EXECUTE AS" clause.
Let me know if you would like to see a sample. I'm happy to share!
Have a good day,
~Katie Lachance-Duffy
Philadelphia Regional Arts Consortium