sp_send_dbmail fails in SQL Server Agent Job

Question

Could someone explain and resolve the following issue. Thanks.

I encountered this query issue when I run it in SQL Server 2012 on Windows Server 2012.
The query (@query) runs very well when it stands alone, but it shows no result when placed into a SQL Server Agent Job (see below).

@query = N'SELECT p.name AS [Name] FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.name = "sysadmin";' 

Intesrestingly, when I tested it in Sql Server 2012 that runs on Windows Server 2008, it ran perfectly both stand alone and in SQL Server Agent Job !!?

Answer 1

I have found the cause and the solution.

It lies on the permission to be granted to the account/user that runs SQL Server Agent. This account must have at least securityadmin server role to have access to database mail profile, otherwise it returns an error or does not send the message at all. To grant it the necessary server role, go to Security > Logins to add that account as new login and then grant the securityadmin server role to it.

Hope this helps anyone who might run into the same issue as I did.