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

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

Re: EXECUTE AS

Show all headers | View raw

 (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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next 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