Why can't I execute msdb.dbo.send_dbmail from a stored procedure being executed in a job?

Question

I have a job scheduled to run on my server. That job executes a stored procedure. Inside that stored procedure I am executing msdb.dbo.sp_send_dbmail. When I execute the stored procedure logged in as an admin it runs fine. When the job runs though, it fails with the following error:

Executed as user: AD\sql_server. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000](Error 22050). The step failed.

I have tried modifying the stored procedure and adding in WITH EXECUTE AS OWNER. When I do this the stored procedure fails with the following error:

Executed as user: AD\sql_server. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000](Error 229). The step failed.

What do I need to do to be able to execute a stored procedure in a job that executes msdb.dbo.sp_send_dbmail?

Answer 1

unfortunately WITH EXECUTE AS OWNER is not going to solve your problem.

You may have to add the user as a part of the built in database mail role with something like:

USE msdb;
EXEC sp_addrolemember 'DatabaseMailUserRole', 'AD\sql_server'

Check out this post.