Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail From: rollscanardly@att.net Newsgroups: comp.databases.ms-sqlserver Subject: Re: EXECUTE AS Date: Thu, 26 Jul 2012 15:52:22 -0700 (PDT) Organization: http://groups.google.com Lines: 39 Message-ID: <106eb77d-821d-41d4-aa2a-e22f7643c225@googlegroups.com> References: NNTP-Posting-Host: 76.235.60.239 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1343343142 16852 127.0.0.1 (26 Jul 2012 22:52:22 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 26 Jul 2012 22:52:22 +0000 (UTC) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=76.235.60.239; posting-account=H0CbGQoAAACbl_tS1RfYwvMt_jmh18zO User-Agent: G2/1.0 X-Received-Bytes: 2664 Xref: csiph.com comp.databases.ms-sqlserver:1183 On Thursday, July 26, 2012 5:27:47 PM UTC-4, Erland Sommarskog wrote: > (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 Thanks. I haven't made it through it all, but can see two workable alternatives already. Fred. Fred.