Email queue not working.

Question

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?

---

Pérez

Answer 1

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.

---

Pérez

Answer 2

Hi Pérez,

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.

Best Regards,
Stephanie Lv 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hi Pérez,

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

 

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 4

Hi All,

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!!!

---

Pérez