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).