November 30, 2009 at 7:09 am
Hi all,
I have an issue that I need help with regarding the 'Execute As' statement in stored procs.
I have a proc (CheckSQLJob) in database1 that does some work and then calls a couple of msdb system procs: sp_verify_job_identifiers and sp_start_job.
I created a test account and gave it temporary sysadmin access on the SQL Server. I then specified that account in the Execute As statement in proc CheckSQLJob.
If I log in with the test acccount and run the msdb procs they execute without error, however, when I execute CheckSQLJob I get an execute permission denied on both MSDB procs.
I thought as I was going across databases it may be an ownership chaining issue and so enabled this feature but it made no difference. I also profiled the server and it was definitely using the new test account I created and so I don't know why I am able to execute system procs directly but not via a proc that uses Execute As. I also removed Execute As and ran CheckSQLJob using my admin test account and it worked correctly.
If anyone can explain what is going on here I would apreciate it.
Paul
November 30, 2009 at 7:34 am
To my understanding an Execute As account which is a limited access account will not have access to the Agent database which is MSDB. I think in SQL Server 2005 and up most Agent based tasks require admin permissions directly or as proxy because Microsoft wants these tasks going back to someone with admin permissions within the system. That does not include an Execute As account.
Kind regards,
Gift Peddie
November 30, 2009 at 8:24 am
Thanks for the reply and info Gift.
That's all I needed to know.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply