0

If I have multiple SQL Servers and I want to display what are the Linked servers to the actual SQL Server (myServer1) I am connected to, I could execute sp_linkedservers or navigate under Server Objects/Linked Servers and that will show me a list of the servers connected to it.

Now let's say I have access to myServer1 only so I can not execute sp_linkedservers , is there a way to display what are the SQL servers that has myServer1 as a linked server?

mcha
  • 2,938
  • 4
  • 25
  • 34

2 Answers2

0

You can use these DMV but you still need appropriate privileges:

use master
go
SELECT *
FROM sys.servers s
LEFT JOIN sys.linked_logins l ON s.server_id = l.server_id
LEFT JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29
  • That would stil list the linked servers to the SQL server I am actually executing the SQL from (myServer1) , I want to do the opposite, shows the list of servers that has myServer1 linked to them – mcha Sep 09 '15 at 11:40
0

your question as i undertsood it: running 'something' on a sql server instance (let's call it A) retrieve the list of other servers/instances that have A as target for a linked server object.

answer: you can't.

a linked server is not a persistent connection so the 'target' server has no information about which server has a potential connection referencing it; the target server will notice the existance of the remote server only when a connection is activated (eg a query is run).

moreover when a connection is activated the target server cannot know if on the other end there is a linked server, an odbc source or any other software object.

you may scrub the logs looking for remote endpoints but then you are on your own trying to guess if on the remote end there is a linked server or anything else.

your best bet is a script (powershell, maybe?) that retrieving the list of sql servers produces a list of the linked servers configured on each one.

Paolo
  • 2,224
  • 1
  • 15
  • 19