Post reply

Securing Access To SQL Agent

  • October 23, 2011 at 11:27 pm

    #249817

    Hi All,

    Just wondering if anyone has any ideas on how I can secure the SQL Agent without using one of the fixed roles?

    The scenario is that I want our software developers to customize the access the dev team has to the SQL Server Agent. I want them to be able to view jobs, view the job history, and to be able to start and stop jobs. I don't want them to be able to alter or add any new jobs to the agent.

    Playing around with the three fixed roles and they all allow new jobs to be added to the system. I was hoping to create a custom role which would restrict the developers down to the permissions I mentioned above. The script I was using looked like this:

    [use msdb]

    create role SQLAgentDeveloperRole

    grant select on dbo.sysjobs_view to SQLAgentDeveloperRole

    grant select on dbo.syscategories to SQLAgentDeveloperRole

    grant select on sysschedules_localserver_view to SQLAgentDeveloperRole

    grant execute on sp_help_category to SQLAgentDeveloperRole

    grant execute on sp_help_job to SQLAgentDeveloperRole

    grant execute on sp_help_jobactivity to SQLAgentDeveloperRole

    grant execute on sp_help_jobcount to SQLAgentDeveloperRole

    grant execute on sp_help_jobhistory to SQLAgentDeveloperRole

    grant execute on sp_help_jobhistory_full to SQLAgentDeveloperRole

    grant execute on sp_help_jobhistory_sem to SQLAgentDeveloperRole

    grant execute on sp_help_jobhistory_summary to SQLAgentDeveloperRole

    grant execute on sp_help_jobs_in_schedule to SQLAgentDeveloperRole

    grant execute on sp_help_jobstep to SQLAgentDeveloperRole

    grant execute on sp_help_jobsteplog to SQLAgentDeveloperRole

    grant execute on sp_help_jobschedule to SQLAgentDeveloperRole

    grant execute on sp_help_jobserver to SQLAgentDeveloperRole

    grant execute on sp_help_operator to SQLAgentDeveloperRole

    grant execute on sp_help_proxy to SQLAgentDeveloperRole

    grant execute on sp_check_for_owned_jobs to SQLAgentDeveloperRole

    grant execute on sp_check_for_owned_jobsteps to SQLAgentDeveloperRole

    grant execute on sp_help_schedule to SQLAgentDeveloperRole

    grant execute on sp_start_job to SQLAgentDeveloperRole

    grant execute on sp_stop_job to SQLAgentDeveloperRole

    This approach works but then the SQL Server Agent node disappears from management studio (which makes it a tad bit hard for the devs to view the jobs!). Does anyone have any ideas on how to solve this?

    Cheers,

    Will

  • October 24, 2011 at 1:53 am

    #1398526

    You could use the builtin SQLAgent roles and deny EXECUTE on msdb.dbo.sp_add_job

    -- Gianluca Sartori

  • October 24, 2011 at 7:04 pm

    #1398947

    Thanks mate you are a genius!

    Just doing some more testing but it's looking very promising. I've set deny on all add / drop / update procs in the SQLAgentUserRole and given a test account access to the SQLAgentOperatorRole. The user can see all jobs on the system, start / stop any jobs on the system, but can't make any changes to any of the jobs.

    I had a minor concern that it may lock the sysadmin accounts out adding / updating jobs but it all looks fine 🙂

    Cheers,

    Will

  • October 25, 2011 at 10:43 am

    #1399223

    Be careful here... Depending on the role and functions, you may be better off using REVOKE instead of DENY.

    DENY overrides other permissions and will prevent access to the object/action in question.

    REVOKE simply removes the existing permission (irrespective of whether that permission was GRANTED or DENIED)

    Thanks,

    Jared

    Jared
    CE - Microsoft

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

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