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
.



Relevant Pages

  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: SQLCE performance from .NET CF v2.0
    ... Please remember when bulk inserting is being executed against SQL CE, ... > local database functionality. ... > database technology and I am considering switching to SQLCE at the same ... > execute the prepared statement again. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: CFS: some bad numbers with Java/database threading
    ... execute multiple queries on the same data, ... workload is "pathological". ... lock-less algorithms (like variations on optimistic locking) because ... if the locking is implemented using database ...
    (Linux-Kernel)
  • Re: Regular users rying to run sp_start_job
    ... WITH EXECUTE AS 'DomainName\martinrk1' AS ... 'sp_start_job', database 'msdb', schema 'dbo'. ...
    (microsoft.public.sqlserver.security)