• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 89 views
  • Users 0 members are here
Related

Calling sp_send_dbmail from ImpUser-run utility

Nick Reilingh
$organization

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.

  • $organization

    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

  • $organization
    Hah! I should have known there was an existing procedure right under my nose. I actually already implemented my own, but it looks like AP_SEND_SMTP_MAIL removes line breaks for some reason, so I think I’ll be sticking with mine.

    Thanks for the tip!
    —
    Nick

    On Apr 6, 2015, at 9:02 AM, Katie Lachance-Duffy <bounce-katielachanceduffy1902@tessituranetwork.com> wrote:

    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

    From: Nick Reilingh <bounce-nicholasreilingh4883@tessituranetwork.com>
    Sent: 4/2/2015 1:54:58 PM

    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.




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!