Post reply

Get Linked Server Configuration

  • July 14, 2008 at 12:57 pm

    #188175

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

    ..>>..

    MobashA

  • September 11, 2008 at 6:28 am

    #870258

    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

  • September 11, 2008 at 8:21 am

    #870338

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

    -John

  • September 11, 2008 at 11:47 am

    #870535

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

  • September 11, 2008 at 11:58 am

    #870542

    1. Thank you for the code.

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

  • September 12, 2008 at 3:50 am

    #870836

    thanks for this replies.

    and the modifications u have made making it better thanks.

    ..>>..

    MobashA

  • September 16, 2008 at 10:03 am

    #872520

    Try the below

    select * from sys.servers where is_linked = 1

    Should provide you all the linked servers.

  • May 23, 2016 at 7:11 am

    #1880745

    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