March 16, 2016 at 6:37 am
What is the best way to provide access to run few sql agent jobs on an instance with out granting [SQLAgentOperator] role.
March 16, 2016 at 7:04 am
SQL!$@w$0ME (3/16/2016)
What is the best way to provide access to run few sql agent jobs on an instance with out granting [SQLAgentOperator] role.
Don't know about the *best* way, but one way is to create a proc to do it which contains an EXECUTE AS to escalate permissions as necessary.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2016 at 5:47 pm
If I were doing this I'd probably use a signed stored procedure to allow someone to execute sp_start/stop_job without the actual permissions, then define what groups / users can start / stop what jobs in a table. Basically you create the procedure, which is then signed with a certificate, which you copy to the 'msdb' database, create a user from it, then put the user in the appropriate role. You can find an excellent discussion of code signing at this link.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
March 23, 2016 at 8:29 pm
Hi Phil, Do you have a script for this?
March 23, 2016 at 10:43 pm
Please help me to fix this procedure. This gives me error
Msg 229, Level 14, State 5, Procedure sp_StartAgentJobAndWait, Line 5
The EXECUTE permission was denied on the object 'sp_StartAgentJobAndWait', database 'master', schema 'dbo'.
Granted user execute permission on this procedure.
Requirement - Permission to run jobs 'Job1' and 'Job2'.
ALTER procedure [dbo].[sp_StartAgentJobAndWait]
@jobName nvarchar(128)
,@maxWaitSeconds int = 360
WITH EXECUTE AS OWNER
AS
IF @jobName IN ('Job1','Job2')
BEGIN
DECLARE @runDate int
,@runTime int
,@waitForJob int = 1
,@secondsCount int
,@jobId UNIQUEIDENTIFIER
,@runStatus int
-- Get the job Id
SELECT
@jobId = job_id
FROM
msdb.dbo.sysjobs
WHERE
sysjobs.name = @jobName
IF @jobId IS NULL BEGIN
-- Job not found
RAISERROR (N'Unknown job: %s.', 16, 1, @jobName)
END
-- START the job
DECLARE @Result int
EXEC @Result = msdb..sp_start_job @jobName
IF @Result= 0 BEGIN
-- Unable to start the job so quit
RAISERROR (N'Could not start job: %s.', 16, 2, @jobName)
END
-- Wait 10 seconds before checking if job is still running
WAITFOR DELAY '0:0:10';
SET @secondsCount = 10
SELECT TOP 1
@runDate = sysjobhistory.run_date
,@runTime = sysjobhistory.run_time
FROM
msdb..sysjobhistory
WHERE
job_id = @jobId
AND step_id = 1
ORDER BY
Run_date DESC,
run_time DESC,
instance_id DESC
WHILE (@waitForJob = 1 AND @secondsCount < @maxWaitSeconds) BEGIN
SELECT TOP 1
@runStatus = run_status
FROM
msdb..sysjobhistory
WHERE
job_id = @jobId
AND run_date = @runDate
AND run_time = @runTime
AND step_id = 0
IF @runStatus IS NOT NULL BEGIN
SET @waitForJob = 0
END
PRINT 'Job is still running…Waiting another 5 seconds.'
WAITFOR DELAY '0:0:05';
SET @secondsCount = @secondsCount + 5
END
IF @runStatus IS NULL BEGIN
RAISERROR (N'The Job [%s] is still running.', 16, 2, @jobName)
END ELSE IF @runStatus = 0 BEGIN --Failed
RAISERROR (N'The Job [%s] failed.', 16, 2, @jobName)
END ELSE IF @runStatus = 1 BEGIN --Success
PRINT 'The job finished successfully'
END
END
ELSE
PRINT'Access Denied'
OR
Please modify the script below to allow execution only for jobs 'Job1' and 'Job2'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_StartSQLJob]
-- Add the parameters for the stored procedure here
(@job nvarchar(128), @maxwaitmins int = 5) --, @result int output)
WITH EXECUTE AS OWNER
AS
BEGIN
set NOCOUNT ON;
set XACT_ABORT ON;
BEGIN TRY
declare @running as int
declare @seccount as int
declare @maxseccount as int
declare @start_job as bigint
declare @run_status as int
set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())
set @maxseccount = 60*@maxwaitmins
set @seccount = 0
set @running = 0
declare @job_owner sysname
declare @job_id UNIQUEIDENTIFIER
set @job_owner = SUSER_SNAME()
-- get job id
select @job_id=job_id
from msdb.dbo.sysjobs sj
where sj.name=@job
-- invalid job name then exit with an error
if @job_id is null
RAISERROR (N'Unknown job: %s.', 16, 1, @job)
-- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
declare @xp_results TABLE ( job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- start the job
declare @r as int
exec @r = msdb..sp_start_job @job
-- quit if unable to start
if @r<>0
RAISERROR (N'Could not start job: %s.', 16, 2, @job)
-- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
WAITFOR DELAY '0:0:01';
set @seccount = 1
-- check job run state
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
while @running<>0 and @seccount < @maxseccount
begin
WAITFOR DELAY '0:0:01';
set @seccount = @seccount + 1
delete from @xp_results
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
end
-- result: not ok (=1) if still running
if @running <> 0 begin
-- still running
return 0
end
else begin
-- did it finish ok ?
set @run_status = 0
select @run_status=run_status
from msdb.dbo.sysjobhistory
where job_id=@job_id
and cast(run_date as bigint) * 1000000 + run_time >= @start_job
if @run_status=1
return 1 --finished ok
else --error
RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)
end
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- original error number.
@ErrorSeverity, -- original error severity.
@ErrorState, -- original error state.
@ErrorProcedure, -- original error procedure name.
@ErrorLine -- original error line number.
);
END CATCH
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply