none
SQLAgentUserRole

    Pertanyaan

  • Hello,

    I would like to grant a user permission to execute a SQL job. I there a way to do this. I know that SQLAgentUserRole grants execute permissions, but I believe that is for all jobs. I would like to restrict this user to one particular SQL job.

    Any help would be appreciated.

     

    CODE SNIPPET:

    use MSDB;
    EXECUTE sp_addrolemember

    @rolename = 'SQLAgentUserRole',

    @membername = 'GLOBAL\USERNAME'


    Dave SQL Developer
    Rabu, 16 Maret 2011 19.00

Jawaban

  • Dave,

    you could wrap call to dbo.sp_start_job  within a stored procedure and just give user execute permission to that procedure

    See:

    http://stackoverflow.com/questions/1984944/grant-permissions-to-run-an-sql-server-job

    Kamis, 17 Maret 2011 15.16
  • You can add him to SQLAgentReaderRole role and but then he can run only owned jobs

    I do not think you need to use SSMS , consider granting EXECUTE permission to dbo.sp_start_job system stored procedure

    USE msdb ;
    GO

    EXEC dbo.sp_start_job N'jobname' ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Kamis, 17 Maret 2011 07.40
  •  

    Hi DaveDB,

     

    Did you checked the link posted by Chirag?

     

    In this way, we could grant EXECUTE permission on the stored procedure that we created for starting the job, and with the option WITH EXECUTE AS OWNER, the job actually is invoked by the job owner when the user executing this stored procedure.

     

    For example:
     

    CREATE PROC Start_Job_Name

    WITH EXECUTE AS OWNER

    AS

    EXEC dbo.sp_start_job @job_name = 'Your_Job_Name'

    GO

     

    GRANT EXECUTE ON Start_Job_Name TO Job_User

    GO

     

    If there are any more questions, please feel free to let me know.

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Senin, 21 Maret 2011 11.43
    Moderator
  • Dave, I see that this thread has gone to sleep, but a couple of comments to focus things already mentioned by Uri, Chirag, and WeiLin.

    1. Granting a user or a whole lot of users with SQLAGENTUSERROLE or SQLAGENTREADERROLE the  rights to execute sp_start_job does not open the starting of that job to all of them.  

    The stored procedure itself will only allow a member of those roles who actually is the owning login of the job to start the job.  The owner can only be a login, it cannot be a group. (Sysadmins and members of the SQLAGENTOPERATORROLE have more rights.)

    2. The code mentioned by Chirag and WeiLin is a way to grant rights to start the job to a wider set of people.  I will repeat the code here to make a point

    USE msdb
    GO
    CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
    -- Executes as the database owner, for msdb that is 'sa'
    WITH EXECUTE AS OWNER
    AS
    -- Hardcoding the job name limits this procedure's control
    EXEC dbo.sp_start_job @job_name = 'MyJobThatAGroupControls'
    GO
    -- Grant Execute rights to your selected users
    GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
    GO
    
    
    

    From that code, you can see that those granted rights to the special stored procedure will be able to execute the job, because the 'sa' (a sysadmin) login has the rights to start any job.  That is why a stored procedure like this needs to be tightly controlled.

    RLF

    Kamis, 24 Maret 2011 14.58
  • OK. Scope:  "1 login can start only 1 job."

    1. If you make Login_A the owner of SQL_JOB_B and also a member of either the SQLAGENTUSERROLE or the SQLAGENTREADERROLE, then 'YES' that login can start the job. 

      (However, it can also stop, reschedule, change the contents, etc. of the job.)  Only 1 login can have these rights, since only 1 login can own the job.  (Jobs cannot be owned by groups or roles.))

    2. If you create a job specific stored procedure, as shown above, you can grant Login_A execute rights to the stored procedure, giving it the effective permissions to start the SQL_JOB_B, but not to do anything else to that job that an owner can do.

      (And, if you also want some other login to be able to start that job, you can also grant it the same execute rights.)

    Does the first option satisfy your need?  If not, does the second option satisfy your need? 

    RLF



    • Ditandai sebagai Jawaban oleh DaveDB Kamis, 31 Maret 2011 19.52
    Kamis, 31 Maret 2011 18.34

Semua Balasan

  • You can add him to SQLAgentReaderRole role and but then he can run only owned jobs

    I do not think you need to use SSMS , consider granting EXECUTE permission to dbo.sp_start_job system stored procedure

    USE msdb ;
    GO

    EXEC dbo.sp_start_job N'jobname' ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Kamis, 17 Maret 2011 07.40
  • Hi Uri,

    Thanks for you response. Using this method am I essentially granting everyone execute permission on this particular job? Anyone can execute?

     

    Thanks


    Dave SQL Developer
    Kamis, 17 Maret 2011 14.55
  • Dave,

    you could wrap call to dbo.sp_start_job  within a stored procedure and just give user execute permission to that procedure

    See:

    http://stackoverflow.com/questions/1984944/grant-permissions-to-run-an-sql-server-job

    Kamis, 17 Maret 2011 15.16
  • Sorry, but I don't understand.

    Dave SQL Developer
    Kamis, 17 Maret 2011 15.18
  •  

    Hi DaveDB,

     

    Did you checked the link posted by Chirag?

     

    In this way, we could grant EXECUTE permission on the stored procedure that we created for starting the job, and with the option WITH EXECUTE AS OWNER, the job actually is invoked by the job owner when the user executing this stored procedure.

     

    For example:
     

    CREATE PROC Start_Job_Name

    WITH EXECUTE AS OWNER

    AS

    EXEC dbo.sp_start_job @job_name = 'Your_Job_Name'

    GO

     

    GRANT EXECUTE ON Start_Job_Name TO Job_User

    GO

     

    If there are any more questions, please feel free to let me know.

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Senin, 21 Maret 2011 11.43
    Moderator
  • Dave, I see that this thread has gone to sleep, but a couple of comments to focus things already mentioned by Uri, Chirag, and WeiLin.

    1. Granting a user or a whole lot of users with SQLAGENTUSERROLE or SQLAGENTREADERROLE the  rights to execute sp_start_job does not open the starting of that job to all of them.  

    The stored procedure itself will only allow a member of those roles who actually is the owning login of the job to start the job.  The owner can only be a login, it cannot be a group. (Sysadmins and members of the SQLAGENTOPERATORROLE have more rights.)

    2. The code mentioned by Chirag and WeiLin is a way to grant rights to start the job to a wider set of people.  I will repeat the code here to make a point

    USE msdb
    GO
    CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
    -- Executes as the database owner, for msdb that is 'sa'
    WITH EXECUTE AS OWNER
    AS
    -- Hardcoding the job name limits this procedure's control
    EXEC dbo.sp_start_job @job_name = 'MyJobThatAGroupControls'
    GO
    -- Grant Execute rights to your selected users
    GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
    GO
    
    
    

    From that code, you can see that those granted rights to the special stored procedure will be able to execute the job, because the 'sa' (a sysadmin) login has the rights to start any job.  That is why a stored procedure like this needs to be tightly controlled.

    RLF

    Kamis, 24 Maret 2011 14.58
  • I would like to thank everyone for your feedback, however I think we have gone beyond the scope of the question. Let me repeat the question again...

     

    Is it possible in SQL 2008 to grant a login, access to execute a specific job? Let's say I would like Login_A to be able to execute ONLY SQL_JOB_B. Is this possible?

     

    Thank you


    Dave SQL Developer
    Kamis, 31 Maret 2011 17.30
  • OK. Scope:  "1 login can start only 1 job."

    1. If you make Login_A the owner of SQL_JOB_B and also a member of either the SQLAGENTUSERROLE or the SQLAGENTREADERROLE, then 'YES' that login can start the job. 

      (However, it can also stop, reschedule, change the contents, etc. of the job.)  Only 1 login can have these rights, since only 1 login can own the job.  (Jobs cannot be owned by groups or roles.))

    2. If you create a job specific stored procedure, as shown above, you can grant Login_A execute rights to the stored procedure, giving it the effective permissions to start the SQL_JOB_B, but not to do anything else to that job that an owner can do.

      (And, if you also want some other login to be able to start that job, you can also grant it the same execute rights.)

    Does the first option satisfy your need?  If not, does the second option satisfy your need? 

    RLF



    • Ditandai sebagai Jawaban oleh DaveDB Kamis, 31 Maret 2011 19.52
    Kamis, 31 Maret 2011 18.34
  • Hi Russell,

    Yes, now I get it! I create a procedure that starts a specific sql job. That will take care of my security issues.

    Thank you!


    Dave SQL Developer
    Kamis, 31 Maret 2011 19.54
  • You won't be able to run the script:

    use MSDB;

    EXECUTE sp_addrolemember
    @rolename = 'SQLAgentUserRole',
    @membername = 'DOMAIN\USERNAME'

    unless you map the user 'DOMAIN\USERNAME' to msdb with at least 'public' role.


    Best regards rypki



    Rabu, 09 Mei 2018 09.52
  • SQLAgentUserRole grants execute only to jobs that the user owns:

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188283(v=sql.90)


    Best regards rypki



    Rabu, 09 Mei 2018 11.24