1

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.

1

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.

1
  • You're running SQL in production without knowing how to manage the service users?
    – phoebus
    Nov 19 '13 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.