September 16, 2008 at 6:25 am
Am I the only one that keeps wanting a Server role that can see, start and cancel jobs that doesn't include SysAdmin privledges on other stuff?
It's annoying. We have someone in our office who needs to be able to monitor jobs on a certain server and we have to give him Sysadmin permissions because he can't see the SQL Server Agent or the jobs underneath it unless he has it. We've experimented with the other roles and none of them do this.
Or does someone know another way around this? We don't need a role that can create or delete jobs, just run them, cancel them, check their history. Maybe modify the job steps if there's a major problem...
@sigh.
September 16, 2008 at 7:24 am
If they are the job owner, they do not need sysadmin permissions.
Make the user db_datareader, db_dtsadmin, SQLAgentReaderRole, and SQLAgentUserRole in MSDB and set them as the owner of the jobs.
September 16, 2008 at 10:47 am
Michael,
I appreciate the response, but it didn't work. The user still can't see any of the jobs or even open the Job Activity Monitor. SQL Server Agent isn't even shown in the SSMS Object Explorer let alone anything underneath it.
September 16, 2008 at 11:46 am
Brandie,
Did the user refresh Object Explorer after you made him a member of the SQLAgentReaderRole? I experimented with a non-sysadmin in my office and she was able to expand SQL Server Agent, open the Job Activity Monitor, and even see job history without being the job owner. The user has to be the owner of a job to execute, stop, and start a job. If the user needs to be able to execute, stop, and start any job, they'll need to be a member of SQLAgentOperatorRole.
Here's what I did:
1. Added a user to msdb
2. Made the user a member of SQLAgentReaderRole (it inherits all permissions of SQLAgentUserRole,
so no need to check that one also)
3. Had the user refresh at the instance level in Object Explorer
Greg
September 16, 2008 at 12:02 pm
I tried instance level refreshing and it didn't work. I'm going to try again tomorrow. It might be a cache refresh issue on the user's PC if other people are able to do this and have it work.
September 17, 2008 at 8:27 am
Well, I figured out what the problem was. I tried assigning all these permissions to a database role and then adding the user to the database role and no matter what I did after that, the user couldn't see SQL Server Agent.
When I switched off the role attachment and put the permissions directly onto the user account, the user could see SQL Server Agent in object explorer just fine.
So any thoughts as to why assigning them to a role and then assigning the user to the role doesn't work?
September 17, 2008 at 9:44 am
Well, that's strange. So, you first tried creating your own database role in msdb? Would you list the permissions or role membership you assigned to it, so I'm clear about what you tried.
Thanks
Greg
September 17, 2008 at 10:45 am
I created a role in msdb called "JobOperator" with the owner of "dbo". I assigned other roles to it rather than specific permissions. The roles assigned are as follows:
SQLAgentUserRole
SQLAgentOperatorRole
db_datareader
SQLAgentReaderRole
db_dtsadmin
Then I added a user's Windows domain account to msdb and chose "public" and "JobOperator" as the roles. This is when I couldn't see the SQL Server Agent submenu.
When I removed JobOperator from that user's account and added the above roles directly to the user's account, though, I could see the SQL Server Agent submenu just fine when I reconnected to the server instance in question.
September 17, 2008 at 1:02 pm
I don't get it. I tried both methods and both allowed the user to see SQL Server Agent. Oh well, as long as it works, eh?
BTW, I think the only role the user needs to be a member of is SQLAgentOperatorRole. That would allow the user to start, stop, and view history of any job in the instance and he wouldn't have to be the owner of the jobs. Modifying a job is something else. None of the msdb database roles will allow a user to do that unless he is the job owner.
If you look at "SQL Server Agent Fixed Database Roles" in BOL, it says that the roles are "concentric" in relation to each other - meaning each lesser privileged role is included in the more privileged roles. Since the user gains access to objects in msdb via the role, it doesn't need db_datareader and db_dtsadmn is only needed if the user will be working with SSIS packages.
Greg
September 18, 2008 at 2:58 am
If you create a new database role, then add one of the system databases roles to your role, you do not inherit the rights of the system role.
You need to add your database role to the system role. This will allow the users you have added to yuor role to use the rights assigned to the system role.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 18, 2008 at 4:05 am
Ed,
I can't add JobOperator to SQLAgentOperatorRole. It doesn't show as available when I try. So how am I supposed to get this to work?
September 18, 2008 at 4:11 am
I just noticed something interesting and annoying.
When I add the system db roles to JobOperator, it makes JobOperator the owner of those schemas. When I go into Schemas and change the schema owner back, it removes the role from the JobOperator mapped role list.
What if I want JobOperator to be a part of the other roles, but not to own the schemas?
Something strange is going on here. This happens on both SP1 & SP2 with all the latest hot fixes.
September 18, 2008 at 9:09 am
Brandie,
Ed is right about adding role members. A role or user inherits the permissions of the role that it's a member of.
When you double-click on a database role to see it's properties, you'll see a section titled "Schemas owned by this role" and, below that, a section titled "Members of this role". I suspect you're checking a box in the top section, which makes the role you're looking at the owner of the schema you check.
What you should do is click on the "Add" button under the "Members of this role" section and browse for the users or roles you want to add. In your case you'd open the properties of SQLAgentOperatorRole, click the "Add" button, browse for and select the JobOperator role and make it a member of the SQLAgentOperatorRole.
Does that make sense?
Greg
September 18, 2008 at 10:53 am
Okay, I see what you're saying now.
Sorry about the dizoid moment. Thanks for your help!
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply