Database mail is not working in SQL Server

Question

I have created a proc to send mail with attachment (attachment is from query result)

while running manually it working fine

Scheduled proc using Job. Where in job it is not sending any mail. Also job is not failing

Checked in sysmail_allitems but there is no entry for job mails but for manual run I can find entry.

I suspect some permission issue on service account which runs job.

Check the SQL account that's executing the job, then execute `EXECUTE AS LOGIN = 'TheSQLLoginHere'; EXEC mySendMailSP;`, and see if an error pops up. It's most likely that that login doesn't have privileges to the mail's SPs. — EzLo, Nov 07 '19 at 07:54
Thanks. It worked out. service account failed to send but i used some other to run proc on job. — vignesh, Nov 08 '19 at 07:54

score 2 · Accepted Answer · answered Nov 08 '19 at 08:05

Sending emails through the database requires some special privileges, it's likely that the account the Job executes under doesn't have them.

Check which SQL account is executing the job, then execute:

EXECUTE AS LOGIN = 'TheSQLLoginHere'; 
EXEC mySendMailSP;

You will see the missing privileges there. You can either supply them or change the account for that Job.

1 Answers1