Hello people,
How are you ?
Lately, I have seen a considerable number of DBA's with doubts about the permissions on the roles of SQL Server Agent and many doubts arise on this topic in Whatsapp groups, among them:
- How to make a non-sysadmin user view jobs?
- How to make a non-sysadmin user create and run jobs?
- How to make a non-sysadmin user can run any job?
- How can a non-sysadmin user change SQL Agent settings?
- Can a non-sysadmin user change any job?
My purpose in this post is to explain the roles of SQL Server Agent and answer these questions.
Knowing SQL Server Agent (MSDB) fixed roles
The first step in answering the above questions is to explain what are and are the differences between fixed SQL Server Agent roles.
SQLAgentUserRole
This is the least privileged role of SQL Agent and allows its users to be able to create jobs and deactivate / alter / execute jobs where their user is the owner of the job (ie that he has created or that some sysadmin assigns to him), but cannot delete job execution history even if it is the owner of this job.
Because of this restriction, members of this role can view only their jobs and view only the Jobs folder and Job Activity Monitor.
Even with this role, I can create / delete / run jobs:
SQLAgentReaderRole
To view role effects SQLAgentReaderRole, I added the user “Usuario_Teste” in this role. The members of this role have the same permissions as the role SQLAgentUserRole, but can view all instance jobs, even if the logged in user is not the owner.
This permission applies only to job view / preview. The role members SQLAgentReaderRole Also they can only run / change / delete the jobs they own.
If you try to run a job where your user is not the owner, even though you are seeing this job, you will come across the error message below:
SQLAgentOperatorRole
This is the most privileged role of SQL Agent. Members of this role can view all SQL Server Agent information and view all instance jobs, as you can see below:
In addition, members of this role can add Operators, configure alerts, manage Proxy settings, and can create jobs and run / enable / disable any instance jobs, even those that are not the owner.
However, even members of this role cannot make changes to jobs that they do not own, such as changing the job's schedule, modifying the Steps tab settings, or any other changes.
Even if you try to make changes using the command line, you will not be able to make changes to jobs where the user is not the owner. Role Members Only sysadmin can make changes to any job in the instance, even those that are not the owner.
Answering the questions
Now that I have explained about the roles of SQL Agent, let's answer the main questions on this subject and eliminate these doubts once and for all from the DBA's mind.
How to make a non-sysadmin user view jobs?
For a user to view jobs created by him, simply add them to the role SQLAgentUserRole. In order for him to view all instance jobs, he needs to be added to the role SQLAgentReaderRole. With this, he can also create jobs and execute / delete / change the jobs created by him.
How to make a non-sysadmin user create and run jobs?
If the user only needs to create and execute the jobs created by him, just add him to the role. SQLAgentUserRole, which is the least privileged role.
How to make a non-sysadmin user can run any job?
In order for the user to be able to run any job on the instance, including jobs where he is not the owner, he must be in the role. SQLAgentOperatorRole, which is the role with the highest level of privilege. Keep in mind that this can also delete job history, disable any job, add / change / delete operators and proxy settings.
How can a non-sysadmin user change SQL Agent settings?
Even the role members SQLAgentOperatorRole cannot make changes to SQL Agent settings. If you try to make any changes to the Agent settings, you will see this error message:
However, this can be circumvented if any member of role sysadmin gives EXECUTE access to the procedure. sp_set_sqlagent_properties:
| 1 2 3 4 5 | USE [msdb] GO GRANT EXECUTE ON dbo.sp_set_sqlagent_properties TO [Usuario_Teste2] GO |
If this permission is granted, the user can make changes to SQL Agent settings even without being from role sysadmin.
How to make a user can only view SQL Agent jobs, not being able to create or change?
In many situations, you need to allow access for other users to view jobs but you do not want them to be able to create or change jobs even if they are using the user themselves as the owner of those jobs. In this case, which role allows you to do this? Unfortunately, none. The least privileged role in SQL Agent, which is SQLAgentUserRole, already allows the user to create and change jobs where he is the owner.
Given this scenario, the DBA Caroline goltara found a creative way to achieve that goal. She gave me the tip to add the user or group to the SQLAgentReaderRole role, so that he can view all jobs, and then deny permission in the SP's for handling jobs, using the commands below:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | USE [msdb] GO -- Bloquear criação DENY EXECUTE ON dbo.sp_add_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobserver TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_agent_add_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_agent_add_jobstep TO [Usuario_Teste2] -- Bloquear alteração DENY EXECUTE ON dbo.sp_update_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_category TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_schedule TO [Usuario_Teste2] -- Bloquear exclusão DENY EXECUTE ON dbo.sp_agent_delete_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobserver TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobsteplog TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_purge_jobhistory TO [Usuario_Teste2] -- Bloquear execução DENY EXECUTE ON dbo.sp_agent_start_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_stop_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_start_job TO [Usuario_Teste2] |
With this, the user will not be able to create / change / delete / execute / stop any jobs, but he will be able to view them all.
How do I get a non-sysadmin user to change any job?
No, it is not possible. By default, only role sysadmin members can make changes to jobs that were created by other users and change the owner of a job.
If it were possible for another user to have this power, it would be a major SQL Agent security flaw since jobs are executed using the user of the job owner, with their privileges. If this job performs any audited action, it is the user of the job that will be written to this audit routine.
Imagine security breach if a role member user SQLAgentOperatorRole, but not a sysadmin could change the owner of a job and put any other user, even a sysadmin, and that job would change incorrect data in Production. The user who would be recorded in the logs would be the user who owned the job and not the one who ran the job.
Another serious security hole could occur where there is a job running in production using a login with db_owner privileges in the database. If a non-role sysadmin user could freely edit Jobs where he is not the owner, he could very well change the step of one of the jobs that are automatically executed in production by a schedule and insert any code there, including granting access to itself in this database.
For these reasons cited above, that Only role sysadmin members can edit the jobs of other users, as well as change the owner of the jobs, not even with the CONTROL SERVER privilege on the instance a non-role sysadmin user can do this.
The only alternative I see for a non-sysadmin user to edit other users' jobs is by creating an application (Web or Desktop) to create this job management interface, where the application's bank user has job privileges. sysadmin on the instance. In this way, access control and permissions would be under the responsibility of the application and users will be able to manage jobs since the application user will be a member of role sysadmin.
That's it, guys.
I hope you have cleared all doubts on this subject.
Any questions, leave it here in the comments.
Hug!
The article is great, congratulations. But here I am still struggling. I'm just studying and everything connects normally with my user, except running the job. I configured as you said in the article, msdb has the 3 roles that you mention, in addition to the db_owner. The insistent error when I try to run is:
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group / user 'BRQ \ alexandrecortez', error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Another great post and very didactic! It also helped me better understand each of the permissions hehe.
Hug.
Luiz Vitor
Luiz Vitor, I hope I helped. I also had questions about this topic, so I made the post. Thanks for the visit. Hug!!
Speaks big Dirceu,
Your post saved me today! Rs For me only the guy belong to the group SQLAgentOperatorRole he could edit jobs, steps and etc in which he is not the owner.
Thanks for the post 😉
Fabio, thank you very much for the feedback. That's what motivates us to always be writing and looking for cool stuff to share. I hope my blog will be useful to you at other times.
Hug.