Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get Linked Server Configuration Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 12:57 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
Comments posted to this topic are about the item Get Linked Server Configuration

..>>..

MobashA
Post #533835
Posted Thursday, September 11, 2008 6:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Post #567663
Posted Thursday, September 11, 2008 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:00 AM
Points: 5, Visits: 197
I agree... those joins should be left joins if you want to see all of the linked servers.

-John
Post #567761
Posted Thursday, September 11, 2008 11:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:44 PM
Points: 1,373, Visits: 394
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 --
Post #567996
Posted Thursday, September 11, 2008 11:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
1. Thank you for the code.
2. It wouldn't be extra to mention that this code will not work in SQL 2000 :)
Post #568006
Posted Friday, September 12, 2008 3:50 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
thanks for this replies.
and the modifications u have made making it better thanks.


..>>..

MobashA
Post #568360
Posted Tuesday, September 16, 2008 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:23 AM
Points: 2, Visits: 20
Try the below


select * from sys.servers where is_linked = 1

Should provide you all the linked servers.
Post #570411
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse