none
Lock down Access to SQL Server Agent SQL 2016R1

    Question

  • Hi I need to restricted access to Agent jobs for BIA Dev tem. Basically SQL Agent  has DB maintenance jobs and SSIS jobs.

    I required them to access/ modify/delete/create SSIS jobs only and deny access  to Maintenance jobs.

    how do I permission lock down for selerate groups .

    I already granted to BIA Team database msdb

    SQLAgentOperatorRole

    SQLAgentReaderRole

    SQLAgentUserRole

    Please advice if any one knows how to lock down to access tot Agent Jobs

    many thanks

    Tuesday, June 05, 2018 11:30 PM

All replies

  • It will also depend upon the ownership of the Jobs.   You may also want to pare back on the Operator Role membership since:

    SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. 

    User + Reader may be sufficient for them to create new jobs and access the Proxies needed to execute the jobs correctly.


    Martin Cairney SQL Server MVP

    Tuesday, June 05, 2018 11:41 PM
  • Hi I know Martin. But Question was How to lock down particular AD group access Maintenance jobs . Basically they should able to  execute, stop, or start all local jobs, delete  their BIA jobs.

    do nyou any way to do that.

    many thanks

    Thursday, June 07, 2018 3:48 AM
    • Execute, stop or start all local jobs
    • Delete the job history for any local job. That exactly what Martin  was talking about. You need to add the user/group to SQLAgentOperatorRole 
    • https://www.mssqltips.com/sqlservertip/1041/sql-server-agent-fixed-database-roles/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 07, 2018 4:36 AM
    Moderator