Regular users rying to run sp_start_job



I have a situation where I would like "regular users", typically users who
just have db_reader and db_writer roles in a specific database to be able to
start a job via sp_start_job. I'm having trouble understanding permissions.

As a test, I have a job called SendMail, which simply sends me an email with
the time the job executed.

If I log in as me in Management Studio (SS2005), and on this test box I am a
member of every server role, the following works:

EXEC msdb.dbo.sp_start_job @Job_Name = 'SendMail';

OK, so I created the following procedure:

CREATE PROCEDURE dbo.Testy
(@Who SYSNAME
,@Context VARCHAR(4000) OUTPUT
,@ErrorString VARCHAR(4000) OUTPUT
) WITH EXECUTE AS 'DomainName\martinrk1' AS
SET NOCOUNT ON;
BEGIN
SET @Context = 'dbo.Testy - ' + @Who + ' - ' +
ISNULL(SUSER_SNAME(),'<NULL>') + ' - ' + ISNULL(ORIGINAL_LOGIN(),'<NULL>');
BEGIN TRY
EXEC msdb.dbo.sp_start_job @Job_Name = 'SendMail';
END TRY
BEGIN CATCH
SET @ErrorString = '|MSDB-ERROR_NUMBER()=' + CAST(ERROR_NUMBER() AS VARCHAR)
+ ' |ERROR_PROCEDURE()=' + ERROR_PROCEDURE() + ' |ERROR_MESSAGE()=' +
ERROR_MESSAGE();
END CATCH;
END;
GO

Now I execute the following:

DECLARE @intReturn INTEGER;
DECLARE @Ctxt VARCHAR(4000);
DECLARE @Err VARCHAR(4000);
EXEC @intReturn = dbo.Testy
@Who = 'User'
,@Context = @Ctxt OUTPUT
,@ErrorString = @Err OUTPUT;

SELECT @intReturn AS UserCall
, @Ctxt AS Context
,@Err AS ErrorString;

PRINT '--User' + CHAR(9) + CAST(@intReturn AS VARCHAR) + CHAR(9) + @Ctxt +
CHAR(9) + @Err;
GO

I get back the following:
--User 0 dbo.Testy - User -DomainName\MARTINRK1 -
DomainName\martinrk1 |MSDB-ERROR_NUMBER()=229 |ERROR_PROCEDURE()=sp_start_job
|ERROR_MESSAGE()=The EXECUTE permission was denied on the object
'sp_start_job', database 'msdb', schema 'dbo'.

OK, I alter the proc, the only change being WITH EXECUTE AS OWNER

--Owner 0 dbo.Testy - Owner - sa -
DomainName\martinrk1 |MSDB-ERROR_NUMBER()=229 |ERROR_PROCEDURE()=sp_start_job
|ERROR_MESSAGE()=The EXECUTE permission was denied on the object
'sp_start_job', database 'msdb', schema 'dbo'.

OK, I alter the proc again, this time WITH EXECUTE AS SELF

--Self 0 dbo.Testy - SELF - sa -
DomainName\martinrk1 |MSDB-ERROR_NUMBER()=229 |ERROR_PROCEDURE()=sp_start_job
|ERROR_MESSAGE()=The EXECUTE permission was denied on the object
'sp_start_job', database 'msdb', schema 'dbo'.

Finally, I change the proc WITH EXECUTE AS CALLER

Job 'SendMail' started successfully.
--Caller 0 dbo.Testy - CALLER - DomainName\martinrk1 -
DomainName\martinrk1 <NULL>

I've tried many variations, but it seems every user fails to execute unless
I go to msdb and specifically make them a user, and exec as CALLER, which is
the default.

What is different about EXECUTE AS CALLER verses EXECUTE AS
'DomainName\martinrk1' when they are the same person? Is there a reasonable
way to call jobs this way, of should I just abandon this approach alltogether?

Thanks in advance, Bob
.