Re: Regular users rying to run sp_start_job
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Fri, 14 Sep 2007 18:01:50 -0400
Bob,
Regular users can only start jobs that they own. There are a few roles in
msdb that you can check out, such as SQLAgentOperatorRole, but that might be
too open for you.
In addition, you could set up an alert to start the SQL Agent job from your
some application (using RAISERROR). If the job is already running. It
takes no particular rights to raise an alert. Read about alerts and jobs in
the Books Online.
RLF
"INTP56" <INTP56@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC786C8A-E6D2-4E01-88FA-F9265B5CFDB3@xxxxxxxxxxxxxxxx
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
.
- References:
- Regular users rying to run sp_start_job
- From: INTP56
- Regular users rying to run sp_start_job
- Prev by Date: Re: Regular users rying to run sp_start_job
- Next by Date: Re: Restore database from within stored procedure
- Previous by thread: Re: Regular users rying to run sp_start_job
- Next by thread: Re: NT Authority\system
- Index(es):
Relevant Pages
|
|
