sp_send_dbmail Error In Microsoft SQL Agent Job

Question

I’m trying to send an email from a scheduled SQL Agent job using sp_send_dbmail and receive the following error:

Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504 Query execution failed: Msg 15404, Level 16, State 19, Server MyServer, Line 1 Could not obtain information about Windows NT group/user 'MyDomain\sqlagent', error code 0x5.

Here is the code from the job step:

DECLARE @SQL NVARCHAR(400)
SELECT @SQL = 'SELECT COUNT(staff_id) FROM Staff'

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@subject = 'Email Alert',
@body = 'Test',
@query = @SQL,
@execute_query_database = 'MyDB'

SQL Agent is running under a domain account [MyDomain\sqlagent]. I granted this user db_owner permission in the MyDB database in addition to adding it as a member of the DatabaseMailUserRole in msdb. Still no luck. If I remove the @query and @execute_query_database parameters it will send a ‘test’ email. However, I need to attach the results from the query.

Any help is appreciated, thanks.

Answer 1

I've run into some strange errors with AD in the past. I would recommend checking that the account you are running this under has it's attributes readable within AD. The quickest way to do that would be to run

exec xp_logininfo 'MyDomain\sqlagent'

and seeing if you get the same error. If you do, check the security properties of the domain account [right click the user in Active Directory > Properties > Security tab] and set Read permissions for Authenticated Users.