donderdag 10 april 2014

Execute Permission Denied on the object sp_send_dbmail when running a SQL Agent job

Who never did get the following error when running a SQL Agent Job (SQL Server 2008 or higher)?
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:
SELECT
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'
This role grants execute rights on the StoredProcedure sp_send_dbmail so the <user> is allowed to execute. You can check this by query:
SELECT DISTINCT
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'
So why still this error?

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?