Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1183

Re: EXECUTE AS

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

EXECUTE AS rollscanardly@att.net - 2012-07-26 12:54 -0700
  Re: EXECUTE AS Erland Sommarskog <esquel@sommarskog.se> - 2012-07-26 23:27 +0200
    Re: EXECUTE AS rollscanardly@att.net - 2012-07-26 15:52 -0700

csiph-web