Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: EXECUTE AS Date: Thu, 26 Jul 2012 23:27:47 +0200 Organization: Erland Sommarskog Lines: 33 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="9c1143bfab35549403b85d70ea1a57d8"; logging-data="16362"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+pD/0Oj551zOUtd+y1hnfa" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:J+Az4JcVoLOeXuUxvEEYCguP7tg= Xref: csiph.com comp.databases.ms-sqlserver:1182 (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