You can give your users rights to check if reports have run or jobs are complete without granting full admin rights. Here’s how you can do so by using SQLAgentReaderRole in SQL Server.
If you have several SQL Agent jobs on your system, it is inevitable that at some point in some of your in-house users or developers will contact you asking if a certain report has ran or if a certain job is enabled. The question is…how do you assign users the rights to see these jobs? In SQL Server 2000, it was difficult to allow users to see the SQL Server Agent jobs on your database server without giving the user sysadmin permissions. SQL Server 2005 makes assigning this permission much easier through the use of new database roles in the msdb database.
- SQLAgentOperatorRole
- SQLAgentReaderRole
- SQLAgentUserRole
SQLAgentReaderRole
The SQLAgentReaderRole is a database role located in the msdb database. It is one of three new roles in this database aimed at allowing the database administrator the ability to assign more granular permissions when it comes to the administration of SQL Agent jobs. Assigning a user or group to be a member of this role allows the user to see any SQL Agent job located on the server instance, even the jobs in which that user does not own. The user can see the job, along with any history saved to the job. However, the group is not allowed to execute the jobs. To add a user as a member of the SQLAgentReaderRole, you can execute the following command:
use msdb
EXECUTE sp_addrolemember
@rolename = 'SQLAgentReaderRole',
@membername = 'username'It is also worth mentioning the other two SQL Agent roles available in SQL Server 2005. The SQLAgentUserRole allows users to create jobs and to manage the jobs that they create. The SQLAgentOperatorRole allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.
SQLAgentOperatorRole
There are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole (found in msdb). Unfortunately SQLAgentOperatorRole grants permissions to run any job (among other things).
Comments