March 30, 2017 at 6:57 am
We have a web based application - user enters data, application calls stored procedure to insert data into table.
After insert trigger fires and at the end of the trigger there is an email that sends notice of changes.
Service account for the application does not have permissions to the email que database.
Stored procedure has a begin try catch block, when the trigger fires and attempts to send the email an error is thrown begin transaction -= 1, commit = 0.
Comment out the try catch block in stored proc, error shows that service account does not have permission to the email db.
Looking for someone to explain to me, exactly why this is happening...the stored procedure insert occurs, the after insert trigger fires and attempt to send email. Why is the service account permissions coming into play at this point, and causing the commit to fail.
Transaction order : Inesert, Commit?
A clear explanation would be most appreciated.
March 30, 2017 at 7:03 am
An after insert trigger is still all part of the same transaction, it just means that the trigger happens after the insertion, rather than before or instead of. If any part of a transaction fails (in this case permission denied), then the whole transaction rolls back.
You'll either need to stop the trigger sending an email when the user making the insert is the service account (use an if statement in the trigger to check the user), grant permission for the service account to send emails, or remove/disable the trigger.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 30, 2017 at 7:35 am
I would consider architecting this differently.
Instead of using a trigger, set up a job which runs periodically (every x minutes), checks for new inserts (I assume that there is a suitable data stamp somewhere) and sends an e-mail if any are found.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 30, 2017 at 11:21 am
ALTER PROCEDURE myproc @param1 WITH EXECUTE AS 'Login with email permissions'
AS
...
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply