Learning never stops when you are a consultant. While doing my recent Comprehensive Database Performance Health Check my client asked an interesting question. He informed that he is not able to see SQL Server Agent node in SQL Server Management Studio (SSMS). What could be the possible cause? I was able to provide him answers and sharing it here. First, let us understand what he meant. If you look closer at two SSMS connection. Do you notice a difference? Yes. As the title says – SQL Server Agent is missing in the second image. So, what are the possible causes?
POSSIBLE CAUSES
- If the Login account connecting to SSMS is not a part of SysAdmin role in SQL Server, then SQL Server Agent would not be visible?
To verify, you can run below query in SQL Server Management Studio and check the output.
Select IS_SRVROLEMEMBER('Sysadmin')
If a value is zero, then you are not a Sysadmin and hence you can’t see SQL Server Agent. Image shows the same behavior.
- Another possible reason would be that SQL Server in not an edition which supports SQL Server Agent. Typically, SQL Server Express edition could be another cause.
To verify, you can run below query in SQL Server Management studio and check the output.
SELECT SERVERPROPERTY('Edition')If above returns “Express” or “Express Edition with Advanced Services”, then you would not see SQL Server Agent node in SSMS.
These are the only two reasons I found due to which you won’t see Agent node in SSMS. If you have found some more causes, please share it with others via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Pinal,
Correct me if I am wrong but you could also check that a user has a SQLAgent role (SQLAgentOperator, SQLAgentUser, SQLAgentReader) to msdb. Having one of those roles should allow you to see the SQL Agent and have permissions to do various actions without having to grant someone sysadmin.
Besides admin, there are other roles that will allow you to view the sa, each having different levels of access
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules.
SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.
These roles are much better, I think you will agree, than granting anyone who needs access to the sa an admin role.
source: https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2017
If all above mentioned permissions are given or the user has all the appropriate permissions on the SQL server instance to manage SQL server Agent, still the user is not able to see the SQL server Agent in SSMS then In some cases your SQL Server Agent is not expandable with a label (Agent XPs disabled). In this case run this code to enable it.
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE
GO
Thank you Gagan Padhy. Ive just resolved my issue with your recommendation. Its just a small correction ‘exec sp_configure’. Thanks once again.
Actually and the single quotes should be double quotes..
It didn’t work for me. I created a test user with SQLAgentOperatorRole role, but cannot see SQL Server Agent. On the other hand, a sysadmin can see it on same instance from same SSMS window. The ‘Agent XPs’ option was 1.
Check to make sure you’re not denying the user the ability to view any database (like I was). That effectively hid the msdb database which I think is what also hid the SQL Agent.
thanks its work
It happens to me too. After reserching alot i couldnt find the answer but finally i tried with with the same viersion of SSMS and that works without noting. I guess there was compatablity issue. it was missed on 2017 DB engine agent job services with 2019 SSMS(18.5) tool and i swiched to 2017(17.9.1) tool. and that works fine
Well done, it has solved my problem. Manay thanks. Great Article, Much Appreciated.
Thank you very much Pinal Dave!
exec sp_configure “show advanced options”, 1;
GO
RECONFIGURE;
GO
exec sp_configure “Agent XPs”, 1;
GO
RECONFIGURE
GO
per comment above fixed the issue. Thank you so much!
In the Express Addition will it just “not be shown in the list”… or is it entirely “not available at all”?
You are absolutely right Joe!
I truly appreciate the information, thank you!!
I have Express Edition (64-bit) i am unable to see Sql Server Agent.
This is helpful, thank you. Can you please help me understand something.
Why is that some SSIS Servers if I log in with my MSID (Windows Authentication) and run Select IS_SRVROLEMEMBER(‘Sysadmin’), the value returned is “1”, but on some servers it returns “0”, and it will only return “1” if I log in with a service account?