Grant execute to ONLY ONE SQL agent job to user

  • Hi,

    How do I grant access to a user who is not a user from 'sa' group to execute just one job : Job1 ? I have used these commands below but now the user has access to execute/view all jobs. How do I limit to just one that I need him to view and execute.

    USE [msdb]

    GO

    CREATE USER [ExecuteTransformationJob] FOR LOGIN [DIR\User1]

    GO

    USE [msdb]

    GO

    EXEC sp_addrolemember N'SQLAgentUserRole', N'ExecuteTransformationJob'

    GO

    USE [msdb]

    GO

    EXEC sp_addrolemember N'SQLAgentReaderRole', N'ExecuteTransformationJob'

    GO

    USE [msdb]

    GO

    EXEC sp_addrolemember N'SQLAgentOperatorRole', N'ExecuteTransformationJob'

    GO

    Thanks,

    PSB

  • You can't easily do this. Jobs are at the row level.

    What you could do is create your own procedures in msdb (script and save in a VCS for DR purposes) that read this job, or execute this job, and then grant a role the rights for your procs, adding the user to a role.

  • Can you provide me with a sample procedure or some helpful links ?

  • I assigned the user permission to start job .

    How does he know when it completes ?

    ALTER PROC usp_StartTransformationJob

    @MyJobName sysname = 'Transformation'

    AS

    DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)

    EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@MyJobName;

    RETURN (@ReturnCode)

    GO

  • OK, sorry, was busy with other work.

    First, if you're doing this for users, you're going to find yourself in a bind at some point. Users leave companies, and new users come.

    So, set up a role first, and a group in Exchange/email, for notifications.

    I'd say you are on the right track for this with your code below. Build a procs that can do these things:

    - start job

    - query job

    - stop job

    Grant rights to a role to execute your procs and add the user to a role. You can add notifications to a job step or to the job (I think, don't have a 2005 instance handy) that send a note to a group in your email system (or an alias). Map that to your user.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply