If your SQL applications can’t send email using database mail (I assume you already have DBMail Account and Profile setup), there are two things to set:
- SQL MANAGEMENT STUDIO > MANAGEMENT > DATABASE MAIL > right click and select CONFIGURE… > select MANAGE PROFILE SECURITY >
- put a check on PUBLIC option
- click on DEFAULT PROFILE (Yes, I know it does not appear to be clickable… but it is) and set it to YES
- SQL MANAGEMENT STUDIO > DATABASES > SYSTEM DATABASES > right click on MSDB and select NEW QUERY > then enter > grant execute on sp_send_dbmail to public and click OK
In my case I was seeing errors like:
The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.
So the fix, or at least the root cause was more obivous than some errors.
This was the same in SQL 2008, R2 and I just had to run through this again in SQL 2012.
Thanks to Darren from www.NeoSystems.com for his fine assistance with this fix.