Give user/domain group only view access on sql server agent role.

Question

Give user/domain group only view access on sql server agent role.

Janda Nel

SSC Eights!

Points: 882
More actions
May 20, 2014 at 8:11 am

#307483

Good afternoon.
I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
Thanks a lot in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

I had a issue with a vendor not being able to see or modify sql agent jobs and this is what I did and this solved the issues with them not being able to see or modify sql agent jobs. Under security for the sql user or domain user, under user mappings, check msdn, under database role membership, there are three roles, SQL AgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole check all three. They will only be able to see the SQL Agent jobs and be able to do what they like.

Hope this helps. Good Luck

MCSE SQL Server 2012\2014\2016

Janda Nel SSC Eights! Points: 882 More actions · Answer 2

the thing is,they only need to view the job and the history of the job and not make any changes to the job.Like edit the schedule or add or delete steps of the job

lkennedy76 SSChampion Points: 13683 More actions · Answer 3

Janda Nel (5/20/2014)
the thing is,they only need to view the job and the history of the job and not make any changes to the job.Like edit the schedule or add or delete steps of the job

Then give them the reader role and the user role. See if that works

MCSE SQL Server 2012\2014\2016

Janda Nel SSC Eights! Points: 882 More actions · Answer 4

Janda Nel

SSC Eights!

Points: 882

May 20, 2014 at 8:39 am

#1714976

will try that and report back

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Granting SQLAgentReaderRole will automatically grant the base SQLAgentUserRole too.

A user who has the base User role can only see jobs that they actually own, they'll see nothing else.

A user who has the Reader role can see all jobs but cannot edit the schedules, job steps, etc

A user who has the Operator role can also start and stop jobs that they do not own.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

Here is some reference material from Microsoft. This will help you out as well.

http://msdn.microsoft.com/en-us/library/ms190926.aspx

MCSE SQL Server 2012\2014\2016

Janda Nel SSC Eights! Points: 882 More actions · Answer 7

Thank you.I will try that out today and report back to you