SQL: EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Software Engineer
Software Engineer used Ask the Experts™
Hello:

My SQL job was running successfully, until I added a second step with the query shown below to e-mail recipients with the data that is returned.

I have added "dbo" as the user for this step and, finally, I'm getting an explainable error message:

EXECUTE permission denied on object 'sp_send_dbmail',
database 'msdb', schema 'dbo'.

How do I fix this?  dbo is the owner of this role already.

What is the remedy for this error?

Thank you!

Software Engineer

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'itemail',  
    @recipients = 'this e-mail address; that e-mail address',
    @query = N'select CUSTNMBR, sum(CASE WHEN RMDTYPAL NOT IN (7,8,9) AND
DOCDATE<= GETDATE() -120 THEN CURTRXAM
WHEN RMDTYPAL IN (7,8,9) AND DOCDATE>= GETDATE() -120 THEN ORTRXAMT * -1 ELSE 0 END) as AMOUNT1
from CEN..RM20101
group by CUSTNMBR
having sum(CASE WHEN RMDTYPAL NOT IN (7,8,9) AND DOCDATE<= GETDATE() -120 THEN CURTRXAM
WHEN RMDTYPAL IN (7,8,9) AND DOCDATE>= GETDATE() -120
THEN ORTRXAMT * -1 ELSE 0 END) > 0;',  
    @subject = '120 And Over',  
    @attach_query_result_as_file = 1;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
this is very common and you need to grant the necessary permission before executing sp_send_dbmail

to do so, try refer to:

SOLVED: Cannot Send Email From SQL Database Mail Even Though The Account is Setup
https://www.urtech.ca/2013/05/solved-cannot-send-email-from-sql-database-mail-even-though-the-account-is-setup/
Distinguished Expert 2017
Check and confirm however you execute this is ran under credentials that has rights.
Thank you, so much, Ryan Chong!  That solved the problem!

Software Engineer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial