unable to query sysmanagement_shared_registered_servers_internal

  • debbie.coates 10909

    SSC Enthusiast

    Points: 193

    HI

     I have just started to use Central management Services.  I've registered a central server, created a Serer Group off that, and then registered several servers under that.

    If I select * from sysmanagement_shared_server_groups, i get 5 entries for each server (Engine, Analysis, Reporting, Intergration etc), but If I try to run Select * from sysmanagement_shared_registered_servers_internal, to get a list of my registered servers, I get nothing returned.

    I have full sysadmin rights on every server as I am a dba, However I have been reading about 2 specific roles for msdb that lets you query the central management servers.  I would presume my admin rights give me all permissions of all roles including ServerGroupAdministratorRole, ServerGroupReaderRole  However If i dont , How would i go about granting  sysadmins rights, and on what werver would I need to add the rights to msdb, the initial one I set up, or on all the registered servers?

    I just dont understand how I cant see the list of registered servers when i run  SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers_internal

    any Help would be appreciated.

  • Sue_H

    SSC Guru

    Points: 90779

    debbie.coates 10909 - Friday, January 18, 2019 5:19 AM

    HI

     I have just started to use Central management Services.  I've registered a central server, created a Serer Group off that, and then registered several servers under that.

    If I select * from sysmanagement_shared_server_groups, i get 5 entries for each server (Engine, Analysis, Reporting, Intergration etc), but If I try to run Select * from sysmanagement_shared_registered_servers_internal, to get a list of my registered servers, I get nothing returned.

    I have full sysadmin rights on every server as I am a dba, However I have been reading about 2 specific roles for msdb that lets you query the central management servers.  I would presume my admin rights give me all permissions of all roles including ServerGroupAdministratorRole, ServerGroupReaderRole  However If i dont , How would i go about granting  sysadmins rights, and on what werver would I need to add the rights to msdb, the initial one I set up, or on all the registered servers?

    I just dont understand how I cant see the list of registered servers when i run  SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers_internal

    any Help would be appreciated.

    Did you trying querying sysmanagement_shared_registered_servers instead?
    Select * from sysmanagement_shared_registered_servers

    Sue

  • debbie.coates 10909

    SSC Enthusiast

    Points: 193

    Yes, i tried that, but it doesn't bring me anything back in my grid

    using
    SELECT * FROM msdb.dbo.sysmanagement_shared_server_groups;
    GO

    SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers
    GO

    brings me results in grid one, but not the bottom one,

  • tripleAxe

    SSCertifiable

    Points: 5605

    Does the list of server groups returned by the first query contain the server groups you have created?

    If it only contains the 5 built in groups then I'm guessing you are running the query on the wrong server.  Maybe on one of the registered servers rather than the Central Management Server.

  • Sue_H

    SSC Guru

    Points: 90779

    tripleAxe - Friday, January 18, 2019 8:35 AM

    Does the list of server groups returned by the first query contain the server groups you have created?

    If it only contains the 5 built in groups then I'm guessing you are running the query on the wrong server.  Maybe on one of the registered servers rather than the Central Management Server.

    Yes - That's likely what it is since the groups were just the five that are there by default. Mine have the groups I've created in addition to the default ones.

    Sue

  • debbie.coates 10909

    SSC Enthusiast

    Points: 193

    That was the issue.  I wasn't running the queries from the Central management Server.  many Thanks for you help on this.

Viewing 6 posts - 1 through 6 (of 6 total)

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