Get Linked Server Configuration

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by mobasha » Get Linked Server Configuration

Get Linked Server Configuration

Rate Topic

Display Mode

Topic Options

Author

Message

mobasha

Posted Monday, July 14, 2008 12:57 PM

Say 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

John H Marsh

Posted Thursday, September 11, 2008 6:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 1,325, Visits: 1,391

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

John-476812

Posted Thursday, September 11, 2008 8:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:15 AM
Points: 5, Visits: 186

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

-John

Post #567761

Mark D Powell

Posted Thursday, September 11, 2008 11:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:39 PM
Points: 1,266, Visits: 343

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

Glen Sidelnikov

Posted Thursday, September 11, 2008 11:58 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 12:08 PM
Points: 371, Visits: 794

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

mobasha

Posted Friday, September 12, 2008 3:50 AM

Say 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

pattabhiy

Posted Tuesday, September 16, 2008 10:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 04, 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 »

Permissions