Get Linked Server Configuration

  • Comments posted to this topic are about the item Get Linked Server Configuration

    ..>>..

    MobashA

  • Hello,

    Thanks for posting this script.

    From my testing though it seems the query does not return results for any Linked Servers that do not explicitly map Logins e.g. in the case where the connection is always made using the Login's local security context.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • I agree... those joins should be left joins if you want to see all of the linked servers.

    -John

  • I modified the posted SQL to use Left Joins as suggested and indeed my Link to Oracle now shows up. It was missing before and I had noticed that the join to logins could be an issue.

    SELECT

    a.NAME

    ,a.product

    ,a.provider

    ,a.data_source

    ,a.catalog

    ,f.name

    ,b.uses_self_credential

    ,b.remote_name

    FROM

    sys.servers AS a

    left JOIN

    sys.linked_logins AS b

    ON a.server_id = b.server_id

    Left JOIN

    sys.server_principals AS f

    ON b.local_principal_id = f.principal_id

    I notice that this query also returns the instance which is the first row in sys.servers. I added a "where a.name <> a.data_source" which filters the instance out but I am not sure this is safe for all cases. Is another or different condition necessary to filter out the instance?

    -- Mark D Powell --

  • 1. Thank you for the code.

    2. It wouldn't be extra to mention that this code will not work in SQL 2000 🙂

  • thanks for this replies.

    and the modifications u have made making it better thanks.

    ..>>..

    MobashA

  • Try the below

    select * from sys.servers where is_linked = 1

    Should provide you all the linked servers.

  • Thanks for the script.

Viewing 8 posts - 1 through 7 (of 7 total)

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