1

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?

Adam
  • 1,483
  • 4
  • 21
  • 50

1 Answers1

2

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.

kmk09k
  • 324
  • 1
  • 5
  • 12