From: esquel@sommarskog.se   
      
    (rollscanardly@att.net) writes:   
   > I have a rather simple stored procedure for starting a batch   
   > job in SQL Server 2008:   
   >   
   > ALTER PROCEDURE [dbo].[BUGSTARTPOST] WITH EXECUTE AS OWNER   
   > AS BEGIN   
   >      DECLARE @rc as int   
   >      SET NOCOUNT ON;   
   >      EXECUTE @rc=msdb.dbo.sp_start_job 'Budget Update'   
   >      RETURN @rc   
   > END   
   >   
   > where I am owner of the database containing the procedure and   
   > a have a sysadmin server role.  When a user, inlcuding me,   
   > with execute permission on this procedure, the result is a message   
   > that  EXECUTE permission was denied msdb.dbo.sp_start_job.  If I   
   > execute the procedure without the WITH clause it runs for me.   
   >   
   > How do I go about permitting a user to execute this stored procedure   
   > under my,or some other administrator's credetials so he or she can   
   > run sp_start_job as controlled by this procedure?   
      
   I have an article on my web site that discusses why the approach   
   above does not work, and which also includes two secure solutions for   
   this precise problem: http://www.sommarskog.se/grantperm.html   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)