Problem Description:

Executed as user: abc\xxxxxx. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

Problem Resolution:

Step#1: First find out permission for object sp_send_dbmail:

Below query can be used to check permission of object sp_send_dbmail.

USE MSDB
GO
SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC
FROM MSDB.SYS.DATABASE_PERMISSIONS DP
JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID
JOIN MSDB.SYS.DATABASE_PRINCIPALS PR
ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID
WHERE O.NAME = 'sp_send_dbmail'

Sample Output:

Step#2: Grant missing permission:

based on above output, we noticed that account with which user was running the job did not have any access on object sp_send_dbmail, so we granted the access and issue is resolved.

USE MSDB
GO
GRANT EXECUTE ON sp_send_dbmail TO <abc\xxxxxx>