I ran into the following problem.
Short environment description:
Server: SQL 2008 + SP2
User : domain\user1 is db_owner of database SSISMetaData and member of the msdb database role DatabaseMailUserRole
User: domain\fraaija is sysadmin on the server
SQL-Agent job contains SQL Transact Step:
On the advanced tab of the step the user domain\user1 is selected on the Run As user box.
When the job is executed the following error is generated:
Executed as user: domain\user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.
Because I granted the DatabaseMailUserRole to the domain\user1 login I started to do some tests to see what was wrong.
Started SSMS and connected to the server with the user domain\fraaija and started a new query where I executed the following same mail command. I do receive the mail as expected, so mail function is working. So lets execute the following and try again:
EXEC AS LOGIN = 'domain\user1'
SELECT system_user -- displayed is the user domain\user1
Again I do receive the mail as expected, so why does this error when using the same command from the SQL-Agent job?
I found a solution to make it work for now but is this preffered? For now I leave it this way to keep working but when you have an alternative solution please let me know.
After altering the database setting with the command:
ALTER DATABASE [SampleDB] SET TRUSTWORTHY ON
Now when the job is executed I receive the mail as expected.
Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter