I have a stored procedure that uses "WITH EXECUTE AS" to allow the querying of some system views, the stripped down syntax is as follows:
CREATE PROCEDURE [dbo].[MyTestProc]
WITH EXECUTE AS 'DOMAIN\UserA'
SET NOCOUNT ON;
When I run this procedure I get the error message:
The SELECT permission was denied on the object 'sysjobs_view', database 'msdb', schema 'dbo'
UserA is a member of sysadmin so I would expect it to be able to access the view and in fact if I comment out the "WITH EXECUTE AS" line and run the stored procedure as UserA it works. Any ideas what might be happening?