sp_send_dbmail fails within a trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » sp_send_dbmail fails within a trigger

sp_send_dbmail fails within a trigger

Rate Topic

Display Mode

Topic Options

Author

Message

Griffster

Posted Monday, December 20, 2010 3:48 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:07 AM
Points: 239, Visits: 645

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 boardbatch	where 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?

Post #1037103

Sean Lange

Posted Monday, December 20, 2010 8:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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 boardbatch	where 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1037226

Elliott Whitlow

Posted Monday, December 20, 2010 9:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #1037259

Griffster

Posted Tuesday, December 21, 2010 5:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:07 AM
Points: 239, Visits: 645

I set the permissions for user 'User1' using:

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

Post #1037629

Griffster

Posted Tuesday, December 21, 2010 5:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:07 AM
Points: 239, Visits: 645

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.

Post #1037631

Griffster

Posted Tuesday, December 21, 2010 5:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 22, 2013 8:07 AM
Points: 239, Visits: 645

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?

Post #1037644

simon.letts

Posted Wednesday, March 20, 2013 9:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 122, Visits: 482

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

Post #1433318

Michael L John

Posted Wednesday, March 20, 2013 11:37 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:38 AM
Points: 742, Visits: 2,088

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
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Post #1433370

Sean Lange

Posted Wednesday, March 20, 2013 12:22 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

Michael L John (3/20/2013)

+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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1433404

simon.letts

Posted Thursday, March 21, 2013 3:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 122, Visits: 482

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.

Post #1433653

« Prev Topic | Next Topic »

Permissions