sp_send_dbmail returns blank email while runs in a job step!

Question

Hi, 

I have a query and I want to be emailed in scheduled job.

My query is worked fine. I put my query and the sp_send_dbmail with filled @body parameter into a Stored Procedure.

Executing mentioned SP is error free and I can receive my email with full result of that query.

But when I put this SP into a job, it sends only a blank email!

I am sure the cause of this problem is related to the SQL Server Agent service user. My SQL Server Agent is set to NT AUTHORITY\SYSTEM and if I change it to Domain\administrator the result email will be full and correct but for other related services and mirroring I can't change it for ever.

I also test it with "run as " parameter and I put in the bigger hole with new errors and security alerts...

Can you guide me about the further security needs for NT AUTHORITY\SYSTEM as SQL Server Agent user? (Its current role is set to sysadmin)

Answer 1

One way is to create a proxy account for the job execution:

Creating SQL Server Agent Proxies

A second way is to change the SQL Server Agent sign-on account to a network account for example:

Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager)

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 2

Thank you for your response,

Unfortunately Job steps that execute Transact-SQL do not use SQL Server Agent proxies.  

I used the database_user_name parameter in the sp_add_jobstep stored procedure to add "run as" user but I get the following error after using "run as user" parameter:

'EXECUTE AS USER' failed for the requested user 'DomainName\Administrator' in the database 'DatabaseName'.  The step failed.

 

Setting the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager) will solve my problem but I can't use it because the 'NT AUTHORITY\SYSTEM' is set into other services that have conversation with SQL server and Agent services.

Answer 3

Unfortunately Job steps that execute Transact-SQL do not use SQL Server Agent proxies.  

SSIS has Execute SQL Task. That way you can use proxies.

BOL: http://technet.microsoft.com/en-us/library/ms141003.aspx

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012