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)
