SQL Server 2016 Cluster - mail works in alerts; sp_send_dbmail dosen't send in jobs

Question

I upgraded from 2008 R2 to a 2016 cluster [Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)]. We have some scheduled emails that happen from the agent that are reporting success as the agent runs them, but no email is sent to recipients. If I take the job out of the agent and run it in the query window it works fine. But if the job executes it, it reports success but no email is generated. Alerts however send out just fine from the agent.

Here is the start of an sp_send_dmail script

Select @asOfDate = yesterday_end from dbo.DTE_udfCommonDates(GETDATE())

SET @s = 'Historical Working Data as of '+CONVERT(VARCHAR(12),@asOfDate,107)
Set @b = @s
Set @f = 'HistoricalWorkingDataAsOf-'+replace(CONVERT(VARCHAR(12),@asOfDate,101), '/', '-')+'.csv'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domain.com',
@body= @b,
@subject = @s ,
@profile_name = 'Default Database Mail',
@query = '.....

Have any suggestions of some configuration we missed? Things to try?

Answer 1

AMtwo Got me to the solution. In this new installation we separated the service accounts that run the server/agent and the agent didn't have access to run the database mail or run scripts in the databases.

I added the agent user to the server security then gave them access to the [DatabaseMailUserRole] role in [msdb]. In addition to that it needed access to the database it was pulling information from.

Thanks for your help.