none
sp_send_dbmail from a trigger - can I run this as a different user other than the context of the user who is doing the insert?

    Question

  • I am creating a trigger for insert into a table.  At the end of the trigger, I want to send an email.

    The problem is the user who is calling the stored procedure that is doing the insert, does not have permissions on sp_send_dbmail

    So when the trigger fires because of the insert, the sp_send_dbmail call is failing due to permissions.

    Suggestions?

    Thursday, April 04, 2013 3:43 PM

Answers

  • Here is how I got this to work:

    - create a wrapper procedure in msdb that calls sp_send_dbmail.  

    - add the user who will be calling the wrapper procedure, into msdb and grant them execute permissions on the new wrapper procedure

    - set the databasemail profile that is being used, as the global default

    bingo - bango, it works

    :)


    Thursday, April 04, 2013 7:47 PM

All replies

  • Here is how I got this to work:

    - create a wrapper procedure in msdb that calls sp_send_dbmail.  

    - add the user who will be calling the wrapper procedure, into msdb and grant them execute permissions on the new wrapper procedure

    - set the databasemail profile that is being used, as the global default

    bingo - bango, it works

    :)


    Thursday, April 04, 2013 7:47 PM
  • Hi shiftbit,

    Thanks for your sharing. Your post will help others have concern with these topics. Thanks for your effort again.


    Allen Li
    TechNet Community Support

    Monday, April 08, 2013 1:41 AM