July 7, 2009 at 8:23 am
I want to create a role for the following requirements:
i) User should be able to only look at the job created by anyone
ii) User should be able only to look at the history of the job created by anyone.
I tried by giving the default roles in MSDB but that were giving more than required priviledges. I also tried creating a new role and added the same securables which was there for default MSDB role, that too didnt work. Please suggest me is this possible and which tables or procs should i give access. I did try giving access to couple tables but the user is unable to see the "Sql Agent" icon there. Pleae suggest. Thanks,
July 7, 2009 at 9:37 am
There are three new roles in MSDB database in 2005 which relate to jobs.
1. SQLAgentUserRole
2. SQLAgentReaderRole
3. SQLAgentOperatorRole
Check out Here for details
July 7, 2009 at 9:55 am
The SQLAgentReaderRole can view job properties and job history for any job. Is the problem that you don't want the members of the role to create their own jobs?
You could try making the user a member of the SQLAgentReaderRole and denying execute permission on sp_add_job.
Greg
July 7, 2009 at 4:09 pm
hmm..i can do that but..like if i set deny permission on that role then it will be applied to all the logins associated to that role..i was trying to replicate the same role by creating a new one..i added all the procs and everything..but still the agent is not visible..am i missing something ehre?
July 7, 2009 at 4:44 pm
Try looking at this post
http://qa.sqlservercentral.com/Forums/Topic451364-146-1.aspx#bm733135.
I was recently asked to do something very similar with DTS and Agent Job view rights in Production. We abandoned the notion for other reasons, but I did learn enough to discover that you are going to have to probably create your own role and tweak it. I found this post immensely helpful.
July 7, 2009 at 6:41 pm
thanks..but the post talks only about DTS...im looking to create a role for the jobs with the respective actions which i mentioned in my initial post..
July 8, 2009 at 4:29 pm
iqtedar (7/7/2009)
hmm..i can do that but..like if i set deny permission on that role then it will be applied to all the logins associated to that role..i was trying to replicate the same role by creating a new one..i added all the procs and everything..but still the agent is not visible..am i missing something ehre?
I meant deny EXECUTE permission on sp_add_job to the user rather than the SQLAgentReaderRole. That wouldn't affect any other members of the role.
I haven't had any luck creating a custom job admin role in msdb, so I think this might be your best bet.
Greg
July 8, 2009 at 4:30 pm
iqtedar (7/7/2009)
hmm..i can do that but..like if i set deny permission on that role then it will be applied to all the logins associated to that role..i was trying to replicate the same role by creating a new one..i added all the procs and everything..but still the agent is not visible..am i missing something ehre?
I meant deny EXECUTE permission on sp_add_job to the user rather than the SQLAgentReaderRole. That wouldn't affect any other members of the role.
I haven't had any luck creating a custom job admin role in msdb, so I think this might be your best bet.
Greg
July 8, 2009 at 4:31 pm
iqtedar (7/7/2009)
hmm..i can do that but..like if i set deny permission on that role then it will be applied to all the logins associated to that role..i was trying to replicate the same role by creating a new one..i added all the procs and everything..but still the agent is not visible..am i missing something ehre?
I meant deny EXECUTE permission on sp_add_job to the user rather than the SQLAgentReaderRole. That wouldn't affect any other members of the role.
I haven't had any luck creating a custom job admin role in msdb, so I think this might be your best bet.
Greg
July 8, 2009 at 4:31 pm
"hmm..i can do that but..like if i set deny permission on that role then it will be applied to all the logins associated to that role..i was trying to replicate the same role by creating a new one..i added all the procs and everything..but still the agent is not visible..am i missing something ehre?"
I meant deny EXECUTE permission on sp_add_job to the user rather than the SQLAgentReaderRole. That wouldn't affect any other members of the role.
I haven't had any luck creating a custom job admin role in msdb, so I think this might be your best bet.
Greg
July 8, 2009 at 4:37 pm
Sorry for the duplicate posts. Every time I hit the "post reply" button I got an error screen and it took me a while to realize that my replies really were being posted.
Greg
July 9, 2009 at 3:53 am
In order to set up a custom role in msdb that inherits the capabilities of SQLAgentOperatorRole (or any other role) you need to do the following...
1) Create your role
2) Add your role as a member of SQLAgentOperatorRole
3) Make any changes to permissions you need for your role (e.g. deny EXECUTE on sp_add_job)
4) Add accounts that need to use the new permission set to the role.
If you add builtin roles as a member to your role, you do not inherit the permisions of the builtin 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
July 9, 2009 at 3:53 am
Duplicate post...
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply