"This is the Captain. We have a little problem with our entry sequence, so we may experience some slight turbulence and then... explode." - Captain Malcolm Reynolds, Serenity
I had a requirement recently on SQL Server 2005 to write a trigger to call a stored procedure to send an email when a certain event happened. So I logged into SQL Server Management Studio and coded up my trigger and stored procedure and executed the update statement that would fire the trigger and I get the nice "Mail queued" message in the results window at the bottom.
Next thing was to execute the application that executed the same update. Upon trying it there, it failed with no readily apparent error code. Now being an old Oracle guy, my grasp of some of the finer points of T-SQL error trapping are not up to par. I'm still learning some of the finer points to the fine art of T-SQL coding - so my rudimentary attempt at error trapping wasn't doing what it was supposed to do.
It suddenly hit me that since the application runs under another user to what my ID was that it must be a permissions error. So I granted execute on the stored procedure to the application owner and tried again. Same thing.
Next step was to queue up SQL Profiler, and trace the execution and I could see that the failure was occurring at the execution of sp_send_dbmail. The next brilliant idea in the thought process was to login to SSMS as the application owner and see what happened.
That's when I got this message:
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
Did a little research on this error message and was reminded that in order to use sp_send_dbmail, a user needs to be granted execute on msdb.dbo.sp_send_dbmail. In order to do that a user needs to be created in msdb with the same name as the user in the target database that we're running against. Also, the user needs to be a member of the DatabaseMailUserRole.
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '' ; GO
So, set that up and ran it again. Still failed. Ah, the pain. There was a different message this time though:
Profile not valid
A little further investigation and I realized that I needed to grant access to the email profile to the user in msdb.
Getting all that done finally made it work.
The long and short of this rambling story is that in order to use sp_send_dbmail, there are several pieces that need to be set up.
- A user needs to be set up in msdb matching the user in the calling database
- The msdb user needs to be added to the DatabaseMailUserRole in msdb
- The user in msdb needs to be granted access to the Mail Profile via the Database Mail Configuration Wizard
- If called from a Stored Procedure, the user needs to be granted execute on the Stored Procedure (but then, that's a given).
References: Troubleshooting Database Mail: Permission denied on sp_send_dbmail Troubleshooting Database Mail: Profile not valid
