June 29, 2016 at 1:59 pm
When I log into an account with Sysadmin role I can see SQL Agent.
I set up a user to have SQLAgentOperator Role, but when they log in they cannot see SQL Agent. I am running SQL 2016 Enterprise Edition.
What am I missing? I don't want to give them sysadmin. They are currently a DBOwner of their DB and the Agent Role.
June 29, 2016 at 2:03 pm
Forgive me if I'm wrong (it's been many years since I was a DBA) but I believe they would also need at least public access to the msdb database.
-- Itzik Ben-Gan 2001
June 29, 2016 at 2:11 pm
By giving their user name the Role which is in the MSDB database they should have access to it.
On another note, I found this post here http://www.handsonsqlserver.com/how-to-grant-view-only-permission-to-users-to-see-sql-server-agent-jobs-and-read-only-access-in-all-databases/ which mentions a few things. I tried the following
USE master
GO
GRANT VIEW SERVER STATE to [username]
That did not work.
However, if I followed it up with
USE msdb
GO
EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = [username]
They all of a sudden see SQL Agent.
So if I do the script with GRANT first and ROLE Member afterwards it works. If I reverse it and add the ROLE member first and GRANT afterwards, user fails to see SQL Agent.
I don't recall ever having to GRANT VIEW SERVER STATE, so not sure why this is required.
June 29, 2016 at 11:17 pm
If you are not granted the needed permissions, you will not be able to see the Agent. You can grant the individual roles to the users: https://msdn.microsoft.com/en-us/library/ms188283.aspx
June 30, 2016 at 6:05 am
If you read my previous email, you would see I did grant the role in the link you sent. Even after the role was granted they could not see SQL Agent. I even granted ALL 3 roles to no avail. Solution was to do the GRANT VIEW.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply