I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet quick and easy to setup (I'm severely limited on time at this point), and the 'ideal' (best practice) solution.
Current SQL Server 2005 Configuration
- `After Insert` Trigger on table executes `sp_send_dbmail`
- Database Mail SMTP Account setup (with successful mail test)
- Database Mail Profile setup (with "Public Profile" ticked)
- "guest" account added to "DatabaseMailUserRole"
I found this post suggesting the "correct" solution and I read up on this article where I discovered that it's a pretty involved solution with too many things that can go wrong it seems, not to mention difficult to maintain.
My thoughts for an alternative solution are these.
1. Setup an Application Role in msdb (this would be my first time using one
and not even sure if it IS a solution :P), and use it to ensure that email
can ONLY be generated from the app and not by connecting directly to the server.
2. Abandon the trigger and instead run a job on the table to send email. Definitely
NOT ideal as I would prefer the email to go out instantly, but at least this
way I would be able to control the user context. Additionally this is similar
to the current mechanism, except it would use a Stored Procedure instead of a
DTS package.
The issues I've been battling these 30 hours and the reason for my inquiry are related to the security context under which the trigger was being executed. When I did my testing, everything worked great because I am a DBA, but when my users used the app responsible for generating the email, it would break because their credentials were not sufficiently high to for the trigger to execute the sp_send_dbmail proc, and therefore it broke the app. (Unfortunately my error handling as the SQL Server level is less than desirable at the moment).
Best Answer
You are far better off simply inserting into a mail queue table in the trigger and having a separate email process handling the mail - either in SQL Server Agent or an external program.
This gives you the benefit of using the trigger as you desire, relatively low latency as the other job can see the mail once it's committed, scalability of managing the mail load independently etc.
You can wrap the mail queueing into the transaction properly. Then the queueing app is reliable if the mail server is temporarily unavailable or if you have email address issues. If mail needs to be resent, it's simply a matter of resetting the sent flag. You can make expiration on mail, so that mail which couldn't be sent to the SMTP won't be retried after a certain time. You can quickly route all mail to a test mailbox. You can do quite interesting things with attachments and other things based on data in the queue without tying up the trigger for extended periods - things like generating reports etc.
So the app offloads the mail responsibility to another component and is free to complete the database transaction and return success to the users, while the mail subsystem gets on with its work ensuring all you want to do with the mail gets done.