sp_send_dbmail

Question

sp_send_dbmail

Old Hand

Points: 308

March 23, 2006 at 5:49 pm

I have a trigger on a table that belongs to a non-dbo schema. The trigger is an after insert trigger and calls the sp_send_dbmail (I want to know if someone inserts a record in that table).

Currently, other than users belonging to sysadmin role, only one user (user: testuser) can insert record into that table. But the user gets the error message

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

I know this is happening because the trigger runs unders the context of the user that causes the trigger to execute. And testuser has no permission to execute sp_send_dbmail

The only workaround I know of is to add testuser to msdb, and add the user to the DatabaseMailUserRole role.

I've tried a few methods, such as wrapping the sp_send_dbmail into a stored procedure belonging to the dbo schema (ie dbo.myprocedure_send_mail) and have the trigger execute dbo.myprocedure_send_mail, didn't work.

Also tried to create the stored procedure with EXECUTE AS OWNER option, didn't work either.

Has anyone else come across this problem?

March 27, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

Gator650 SSC Veteran Points: 205 More actions · Answer 1

I am having this exactly problem. I'll post as soon as I figure out my fix for it. I'm going to try to assign the 'datawrite' role as an executer of this since all my users that hit the table are part of 'datawrite'

Near Kindest Regards, Gator

gs-627439 Grasshopper Points: 19 More actions · Answer 2

gs-627439

Grasshopper

Points: 19

February 1, 2008 at 12:18 am

#774073

ALTER DATABASE [database name] SET TRUSTWORTHY ON

Koenraad Dendievel SSC Veteran Points: 289 More actions · Answer 3

http://stackoverflow.com/questions/530320/how-to-execute-spsenddbmail-while-limiting-permissions/536028#536028

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 4

Why don't you store the results that you want to mail out in a table and set up a job to run sp_send_dbmail against this table - Presumably a couple of minutes on the email wouldn't make any difference ?

Koenraad Dendievel SSC Veteran Points: 289 More actions · Answer 5

This is what I meant with the mail_queue table and an agent that sends periodically

Raj Gujar SSC Veteran Points: 213 More actions · Answer 6

I had a similer issue , I was using Service Broker and was trying to send email after the Job of the Queue was complete. Iwas getting the error "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'."

I alter the database

ALTER DATABASE [xyz] SET TRUSTWORTHY ON and my problem was resolved.

Raj Gujar

belgarion SSC-Addicted Points: 493 More actions · Answer 7

Thanks to gs-627439 and Raj Gujar ... After following all the MS supplied instructions the one that was missing was the ALTER DATABASE one. Makes sense I guess given where MS is taking SQL Azure.