Sql – Sending Email from SQL Server Agent scheduled job

agentemailsql

I've scheduled a job in SQL Server Agent that sends the result of a query in an email. If I execute the stored procedure from SSMS, it works fine. But the SQL Server Agent is not able to send emails.

Here is the error I received.

The following account is scheduled as owner of SQL Server Agent in the SQL Server Configuration Manager: NT AUTHORITY\NETWORK SERVICE.

What can I do to assign privileges to this user?

Message

Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Best Answer

You can also try granting access to xp_sysmail_format_query to NT AUTHORITY\NETWORK SERVICE:

USE master
--Create user in master if necessary
CREATE USER [nt authority\system] FOR LOGIN [nt authority\system]
GO
--Grant execute to the stored procedure that's failing
GRANT EXECUTE ON [xp_sysmail_format_query] TO [NT AUTHORITY\NETWORK SERVICE]
GO

Best thing, though, is to run the SQL Agent service under the context of a domain user that has the appropriate access to your network resources. Doing so is much more portable than using local accounts.

Related Topic