July 14, 2020 at 3:02 pm
Hello~
I'm sure there's a very simple solution, but it seems to be eluding me -- I have a db application (MS Access Front End/Azure SQL Back End) -- I have triggers written on certain tables (for example, if an error occurs, a record is added to an error log table) that send an email to me after a record is added to that table. When the records are being added by me, or by a job, everything works fine.
When an end user tried to add a record (using the same example, an error occurs, my code attempts to add a record to an error log table), it fails, and it fails entirely because of the trigger. When I remove the trigger, or even just the actual mail sending portion of the trigger, the record is added to the table.
Login to SQL Server is through windows authentication, however all the end users are lumped in together in windows group, so the user list for the db is something like this:
sa, OURCOMPANY\jsmith, OURCOMPANY\fjones, OURCOMPANY\endusergroup
I suppose I could have a job running every 5 minutes that checks if records are being added to certain tables, and then execute sending an email through that job, but there must be some simple setting I'm missing.
Thanks for taking the time to read this -- any thoughts would be greatly appreciated.
July 14, 2020 at 3:43 pm
Is SQL Expecting the user to have permission to the send_mail procedure ?
July 14, 2020 at 3:54 pm
It appears so -- I had an end user log into my machine, and through MS Access tried to add a record to one of the tables in question and the access error I got was 'ODBC Call Failed - 3155 -- sp mail.....' or something to that effect. And again, this does not happen once I remove the trigger to send mail.
So my guess is yes -- the user needs permission to send email -- I've been unsuccessfully trying to figure out how to give everyone the proper permissions.
July 14, 2020 at 5:03 pm
You need to grant the users access to database mail - to do that you need to add the group to the msdb database and add them to the DatabaseMailUserRole.
Note: this gives all of the users access to that...if you don't want to do that then create a separate account with those permissions and execute the procedure using that account. Or better yet, create a stored procedure that calls sp_send_dbmail - create a credential and 'sign' the procedure and give that credential the necessary permissions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply