SQL Agent missing in SSMS

Question

SQL Agent missing in SSMS

chung-wah.wong

SSC-Addicted

Points: 411
More actions
June 29, 2016 at 1:59 pm

#319601

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.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

chung-wah.wong SSC-Addicted Points: 411 More actions · Answer 2

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.

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 3

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

chung-wah.wong SSC-Addicted Points: 411 More actions · Answer 4

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.

SQL Agent missing in SSMS

Cookies on SQLServerCentral