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 Answer 1

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, 2013 at 5:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .