SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_send_dbmail fails within a trigger


sp_send_dbmail fails within a trigger

Author
Message
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 713
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63997 Visits: 17974
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 Modens 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)
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24592 Visits: 5314
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
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 713
I set the permissions for user 'User1' using:

USE msdb
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '[User1]';
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 713
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.
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 713
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?
simon.letts
simon.letts
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 606
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
Michael L John
Michael L John
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6065 Visits: 8230
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/
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63997 Visits: 17974
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.

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)
simon.letts
simon.letts
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 606
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search