CODEWITHSUNDEEP
sql-serveremailtriggers
Keeno
Keeno

Reputation: 1686

Sending mail on a TRIGGER stops insert from taking place (and email doesnt send)

I have a simple trigger on INSERT

ALTER TRIGGER [dbo].[SendErrorEmail]
   ON  [dbo].[tblNewErrorLog]
   FOR INSERT
AS 
BEGIN   


    EXEC msdb.dbo.sp_send_dbmail 
        @profile_name='myprofile',
        @recipients='[email protected]',
        @subject='error in the database',
        @body = 'check it out'

END

So, the error is obviously something to do with the email, maybe security? I have tried logging in the the credentials used by the service to connect to the DB, and It works if I add a row manually.

I am also a bit worried that even if the email doesn't sent, the insert fails, why is that?

Thoughts on where to start troubleshooting this are most welcome.

Thanks!

Edits: Removed try catch from code general Tidy up

Upvotes: 0

Views: 1023

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294227

The trigger is executing in a context that does not have EXECUTE permission to the msdb.dbo.sp_send_dbmail procedure. Most likely you are falling into the constrained EXECUTE AS sandboxing, see Understanding Execution Context. This would manifest exactly as you describe the problem: it works from SSMS but not in your application.

See Call a procedure in another database from an activated procedure for an example how to use code signing to grant execute permission to code running in EXECUTE AS context sandbox.

If the code is not running under EXECUTE As then is just a simple matter of permissions. You can either grant [msdb] access and EXECUTE permission on sp_send_dbmail to the service account that you application uses, or you can again use code signing as above.

Upvotes: 2

Related Questions