SQL SERVER – SQL Server Agent Missing in SQL Server Management Studio (SSMS)

SQL
9 Comments

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?

SQL SERVER - SQL Server Agent Missing in SQL Server Management Studio (SSMS) agt-miss-01

SQL SERVER - SQL Server Agent Missing in SQL Server Management Studio (SSMS) agt-miss-02

POSSIBLE CAUSES

  1. 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')

SQL SERVER - SQL Server Agent Missing in SQL Server Management Studio (SSMS) agt-miss-03

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.

  1. 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)

, , , , ,
Previous Post
Data Privacy – Are Your Database Processes Fully Compliant?
Next Post
SQL SERVER – What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it?

Related Posts

9 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Grace McCalman
    July 9, 2019 11:24 am

    Thank you Gagan Padhy. Ive just resolved my issue with your recommendation. Its just a small correction ‘exec sp_configure’. Thanks once again.

    Reply
  • Grace McCalman
    July 9, 2019 11:25 am

    Actually and the single quotes should be double quotes..

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • thanks its work

    Reply
  • 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

    Reply

Leave a Reply

Menu