maandag 20 juni 2011

Using sp_send_dbmail from a SQL-Agent job gives error EXECUTE permission denied.

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

[http://technet.microsoft.com/en-us/library/ms187861.aspx]

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

Geen opmerkingen: