Error with sp_send_dbmail in a Job

[BrooksMT]

I have a SP, called SP_Test which includes the msdb SP sp_send_dbmail.

When I run SP_Test from the Stored Procedure folder, it works perfectly fine and ends by sending an email.

But when I run SP_Test from a Job in SQL Server Agent, I get this error when it hits the sp_send_dbmail:

Executed as user: PROD\myuserid. The EXECUTE permission was denied on the object 'sp_send_dbmail'<c/> database 'msdb'<c/> schema 'dbo'. [SQLSTATE 42000] (Error 229).

I have tried to fix it by adding my userid to Users in msdb and gave myself role membership in DatabaseMailUserRole, SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole.

The error continues. Any suggestions appreciated.

 

[gk53]

When SQL Agent run your procedure it rund not under your id it is id from SQL Agent configuration (usially system service account) which does not have enough permissions...

 

[BrooksMT]

I think that might have been true initially. On my first run, the the error said "Executed as user: PROD\SVCQRMSQLAG".

So I went into the job and changed the "Run as" to my userid.

Now the error message says "Executed as user: PROD\myuserid" as noted in my OP.

I've added permissions for myuserid. Not sure what else need doing ...

 

[SQLBill]

This is from the BOL...

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.


Can you confirm you have a mail profile set up and that your login has access to it?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[BrooksMT]

Thanks everyone for the posts.

This turned out to be a problem because there was an (optional) attachment to the email message. This required some extra permissions in the master and/or msdb databases (can't remember right now - it was last week).

I have a to-do to go back and document exactly which permissions were needed.

Brooks