Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail fails within a trigger Expand / Collapse
Author
Message
Posted Monday, December 20, 2010 3:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
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
Posted Monday, December 20, 2010 8:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1037226
Posted Monday, December 20, 2010 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Posted Tuesday, December 21, 2010 5:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
I set the permissions for user 'User1' using:

USE msdb
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '[User1]';
Post #1037629
Posted Tuesday, December 21, 2010 5:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
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
Posted Tuesday, December 21, 2010 5:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:18 AM
Points: 247, Visits: 698
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
Posted Wednesday, March 20, 2013 9:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:23 AM
Points: 141, Visits: 522
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
Posted Wednesday, March 20, 2013 11:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 969, Visits: 3,028
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
Posted Wednesday, March 20, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433404
Posted Thursday, March 21, 2013 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:23 AM
Points: 141, Visits: 522
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 »

Add to briefcase

Permissions Expand / Collapse