How to allow access to non sysadmin users to view status of sysadmin owned scheduled jobs?

Printer friendly

In SQL Server 2000 only sysadmin (sa) privileged users can manage the scheduled jobs under SQL Agent tasks. In general any user can create a job, but a job can be edited only by its owner or members of the sysadmin role. The user can also view its history. Viewing a job's history allows you to see when the job ran, the status of the job as a whole, and the status of each job step in the job. You can see whether the job ever failed in the past, when the job last completed successfully, and what output the job created each time the job ran. Members of the sysadmin fixed server role can view or modify any job, regardless of the owner.

There will be a requirement where a business user in your organisation is required to attain the status of set of job(s) that are owned by sysadmin which are important for their day-to-day business operations. It will be a tough-task for the Database Administrator to grant ADMIN access to normal users.

In order to accomplish this in SQL Server 2000 the DBA must add the user to TargetServersRole role in MSDB database. Prior to Service Pack 3 on SQL Server 2000 the user must be added to the sysadmin group in order to get a chance to view the jobs that are owned by sysadmin group.

For the same scenario in SQL Server 2005, the MSDB database has incorporated fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

In SQL Server 2005 to give others ownership of a job from SSMS (SQL Server Management Studio), edit the appropriate job properties and in the Owners list select the login.

A breif description from SQL Server Books Online about these 3 roles in SQL Server 2005:

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)