send_db_mail with attachment query can't see user databases

Question

I have a SQL Agent job that calls send_db_mail with the following code:

DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Admins',
    @recipients = '[email protected]',
    @body = 'Attached are the results of the query.',
    @query = 'SELECT columns from MyDatabase.dbo.MyTable',
    @subject = 'Test Run',
    @attach_query_result_as_file = 1 ,
    @query_attachment_filename = @FileName,
    @query_result_separator = ',',
    @query_result_no_padding=1,
    @query_result_header = 0,
    @query_result_width = 32767,
    @exclude_query_output=1,    
    @append_query_error = 1;

When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.

I have checked the following:

• Nothing appears in sysmail_allitems
• No errors appear in sysmail_event_log
• If I change @query = 'select 1 as squirrel' it will send an email from the job.
• If I change @query = 'select name from master.sys.databases' it will send an email from the job.

There is no error message given when no email is sent, and the job even lists as succeeded.

I'm not sure where to look next - can anyone suggest where to look for problems?

Answer 1

When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.

Maybe they have different rights ?

Answer 2

Has been a while, but in which context was the job (step) executed? When you execute the script in SSMS you are executing in the context of your login, which does have sufficient privileges.

SQL Server Agent Service Permissions

If the job is executed in the context of the SQL Server Agent Service account, then the statement might not have been executed due to missing permissions.

The SQL Server Agent Service uses the NT SERVICE\SQLSERVERAGENT account, only if your service has been set up to run with the default account during setup.

If you are using a dedicated <domain>/<user> account, then that account has to have a corresponding SQL Server Login in your SQL Server instance.