Post reply

Sending an email from inside a trigger...

  • November 5, 2011 at 4:23 pm

    #247463

    A VP has requested to be notified when a column in a table has been modified. The trigger works fine when "I" run it (as I'm a sysadmin) but when anyone else outside our DBA group attempts to update the table and error gets thrown:

    Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1

    The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

    I'm not big on security so I'm not really sure how to correct it. I've tried using WITH EXECUTE AS... inside the trigger but that's not working either.

    Any suggestions?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • November 5, 2011 at 6:34 pm

    #1403675

    Does the service account have required privileges?

  • November 6, 2011 at 5:00 am

    #1403698

    Have a look at the following article from Jonathan Kehayias

    http://www.sqlservercentral.com/articles/Security/68873/

    This sets out the possible methods for using sp_send_dbmail

  • November 7, 2011 at 10:07 am

    #1404073

    Viiki.seth, yes the service account has the proper permissions.

    Patrick, the article was good and don't know how I missed that when i searched for it on this site 🙂 I tried it and while it did get rid of the error for my test account, it remained for all the users in the Development group. In fact, because of way it blows up, now they can't even update the table (prolly should throw a TRY..CATCH in there somewhere...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • November 7, 2011 at 10:51 am

    #1404100

    It's all permissions, of course, so i believe you need one of two things: either add every login as a user to the msdb database(or at least the ones that would be connecting to that applicaitons database as non-sysadmins) , and then put those uses in the built in "DatabaseMailUserRole"

    alternatively, you could have the trigger run with EXECUTE AS for a login that does have permissions to that role (as well as the objects touched in the trigger itself)

    the trigger is executed as teh user that (i assume) has insert/update on the table...but that user doesn't have that databasemail role.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 7, 2011 at 11:18 am

    #1404115

    Thanks Lowell (and everyone). I went into the msdb db and added a the development group to the mail profile and it worked!

    Issue resolved.

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply