1. listopadu 2011 21:53
Error Message: The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
I thought I had fixed this problem and was right! I did! but somehow it is not working again. We have a user 'Bob' which has sysadmin and public roles. This user is used for the permissions for our email job to run. The job calls a sproc and that sproc has a cursor in it that runs through the table and then sends the emails. The job is working fine and every 10 minutes like clock work we get this same error message. It was working just fine as it currently is but now isn't. Any past experience with this?
1. listopadu 2011 23:14
An update on this one. So I tested the procedure and it works when I run it manually (emails sent out). The jobs runs and completes successfully but it's not working (emails aren't being sent out). Could the job have gotten corrupted some how? I have checked it over and even to the extent of scripting it out and going line by line. This is a wierd one.
3. listopadu 2011 3:22Moderátor
According to error message, it is a security issue while sending Database Mail. Basically, the user is required to be a member of DatabaseMailUserRole database role in the msdb database. However, there are scenarios that it is not enough to work around this issue. You may have a try to use the certificate signed stored procedure. Please have a look at this article on this topic in detail: Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
9. listopadu 2011 21:35Moderátor
Can you check the following?
1) Enable Verbose logging in SQL Agent http://blogs.msdn.com/b/sqlagent/archive/2010/10/15/turning-on-verbose-logging-in-sql-agent.aspx
2) Review restart agent logs and review agent logs
3) Check if database mail profile is enabled for SQL Agent ( SSMS -> SQL Server Agent -> Properties -> "Alert System")
4) In this dialog,
make sure that MailSystem is set to "Database Mail"
Mail Profile is set to a valid profile
5) Restart SQL Agent ( in next release of SQL Server, you dont need to restart, settings are refreshed automatically)
6) After agent restart, create an operator
7) Create a job that notifies operator on success / failure / completion
Let us know if this helps. If not , email me
Sethu Srinivasan [MSFT]
3. května 2012 18:42
So wanted to reply on this as well. It's been a while but anyways... So the problem was on a non-production environment and the DBA said that the user that was being used had all the permissions. It didn't!!! Anyways this was a simple user issue. We switched to a user we knew had permissions since one of the other email scheduled jobs was under this other user and bam it worked. I was like SMH!!!