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

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

EXECUTE AS

Show all headers | View raw

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?

Fred.

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