Message
Executed as user: <user> The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.
Current at my customer after moving some databases and SSIS packages to an other server we did get this error again.
The user that was specified as Run AS user in the SQLAgent Job is already a security principal in the MSDB database and even member of the MSDB databaserole: DatabaseMailUserRole
You can check this by query:
SELECTThis role grants execute rights on the StoredProcedure sp_send_dbmail so the <user> is allowed to execute. You can check this by query:
T2.name
, T3.name
FROM sys.database_role_members T1
INNER JOIN sys.database_principals T2
ON (T1.role_principal_id = T2.principal_id
)
INNER JOIN sys.database_principals T3
ON (T1.member_principal_id = T3.principal_id
)
WHERE
T2.name = 'DatabaseMailUserRole'
SELECT DISTINCTSo why still this error?
T1.name
, T1.type_desc
, T2.class_desc
, T2.permission_name
, T2.state_desc
, CASE
WHEN T4.type_desc IS NULL OR T4.type_desc = 'SYSTEM_TABLE' THEN T2.class_desc
ELSE T4.type_desc
END
, Isnull(T3.name, Object_name(T2.major_id))
FROM sys.database_principals T1
INNER JOIN sys.database_permissions T2
ON T2.grantee_principal_id = T1.principal_id
LEFT JOIN sys.schemas T3
ON T2.major_id = T3.schema_id
LEFT JOIN sys.objects T4
ON T2.[major_id] = T4.[object_id]
WHERE T1.type_desc = 'DATABASE_ROLE'
AND T2.class_desc <> 'DATABASE'
AND T1.name = 'DatabaseMailUserRole'
Some additional information, in the SQLAgentJob there is an step that executes as StoredProcedure "usp_SendETLAuditMail" that is created in the database DWHFrameWork.
This StoredProcedure just collects some audit information and a list of endusers and ETL developers that need to receive an message when the SQLAgent Job is executed. In this StoredProcedure the sp_send_dbmail is used by EXEC msdb.dbo.sp_send_dbmail......
In the SQLAgent Job step the database is set to DWHFrameWork and thats the clue.... the user has permission but the database that is holding the StoredProcedure isn't trusted....
SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DWHFrameWork' shows 0
I don't know an other way yet but my solution for now is simple:
ALTER DATABASE DWHFrameWork SET TRUSTWORTHY ON;
And all is working fine, but is this really secure?