Sql-server – send_db_mail with attachment query can’t see user databases

database-mailsql serversql server 2014

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 = 'my@email.address.com',
    @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?

Best Answer

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 ?

Related Question