sp_send_dbmail fails within a trigger

  • I'm trying to write a trigger that will inform me via an email using sp_send_dbmail when a certain value has been set on a field either by update or insert by an application a colleague has written for me. If you see the code below, it all looks syntactically correct as far as I know (although this is probably the first trigger I've ever written so may be wrong).

    CREATE TRIGGER dbo.board_batch_UPDATE

    ON dbo.boardbatch

    WITH EXECUTE AS OWNER

    AFTER UPDATE

    AS

    IF (select count(*) from boardbatchwhere graphgroup = 'BAR') = 2

    Begin

    Exec msdb.dbo.sp_send_dbmail @profile_name = 'DRU Work Mail', @recipients = 'joe.bloggs@company.co.uk', @subject = 'More than one with graphgroup BAR in boardbatch',

    @Body = 'Run select * from boardbatch where graphgroup = BAR'

    End

    go

    When I run the application and set another graphgroup to BAR I get the following error:

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

    On researching this error, I've seen this suggested quite regularly:

    USE msdb

    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '[User1]';

    where User1 is the username being used by the application to run the updates. Having added the user to the msdb database and run the above statement, I still get the same error.

    Any ideas?

  • Who is the owner in this case and does that account have permission to execute sp_send_dbmail?

    For the trigger itself, It looks syntactically correct but I am not sure the logic is really what you are looking for.

    IF (select count(*) from boardbatchwhere graphgroup = 'BAR') = 2

    Do you only want to be notified when the count = 2 or do you want to be notified when any group gets update to 'BAR'? You should probably look at using the inserted table for this.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'm *guessing* but does the owner of the database or the caller exist as a user in the msdb database and exist in that role? You are crossing a database boundary and unless you have user chaining on it is likely to fail. Also, who is the owner of the database? Have you normalized all of those, in my production environments I make sa the owner of every database and I don't like turning user chaining on.

    CEWII

  • I set the permissions for user 'User1' using:

    USE msdb

    EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '[User1]';

  • Forgot to mention, one record already has this set to BAR. I want to be alerted as soon as another one has it set. Not too interested about if there are more than 2 really.

  • Correct, the user exists as a user in the msdb database and I've made sure it exists in the DatabaseMailUser role in msdb. The owner of the database is the owner of all my created databases, but not msdb. msdb's owner is sa. I'm not aware of user chaining sorry. Should I switch it on/off?

  • Hi, did you get this working as I have the same issue. Was DB chaining the solution? See properties --> options of DB, then "Cross DB ownership chaining enabled", set to True

  • This is probably one of the "worst practices" in a trigger.

    For starters, you need to elevate secutity.

    Secondly, the amount of overhead that occurs with each Insert/update/delete is significant.

    You may want to explore inserting a record into a "message" table in the trigger, and have a different process scan this table for changes or additions. If there is somehting to do, the process sends the email.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John (3/20/2013)


    This is probably one of the "worst practices" in a trigger.

    For starters, you need to elevate secutity.

    Secondly, the amount of overhead that occurs with each Insert/update/delete is significant.

    You may want to explore inserting a record into a "message" table in the trigger, and have a different process scan this table for changes or additions. If there is somehting to do, the process sends the email.

    +1000

    Using another table is a far better approach for this sort of thing. You don't want to have a modification to your table fail because the email encountered an exception. And you don't want to make a CRUD operation wait while the system generates and sends an email.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks, all good points. The table I'm placing the trigger on though is a password table that an app uses, its seldomly accessed, but I want to alert myself when someone attempts to view our passwords. Yes there is security and encryption in place no the table, but unfortunately other IT staff have access to this software so I wanted to put smoething else in place. I wouldn't use this practice for a table with many ins\dels\upds, but for this instance it's ideal.

Viewing 10 posts - 1 through 9 (of 9 total)

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