Query Linked Server Logins

Question

I'd like to produce a listing of linked servers and their mapped logins. Anyone know how? sys.sysservers will show servers, but no login information is presented.

Answer 1

This query will give you a listing of all the linked servers and their logins:

-- linked servers and their logins
select s.name, p.principal_id, l.remote_name
from sys.servers s
    join sys.linked_logins l
        on s.server_id = l.server_id
    left join sys.server_principals p
        on l.local_principal_id = p.principal_id
where s.is_linked = 1
go

Add more columns if you need more information. Here are the base DMVs:

-- list the linked servers
select * 
from sys.servers
where is_linked = 1
go

-- linked server logins
select * 
from sys.linked_logins
go

-- local logins
select * 
from sys.server_principals
go

Answer 2

And a quick trace told me the answer:

sys.linked_logins

And a query to find more info:

SELECT  sp.name ,
        s.name ,
        ll.remote_name
FROM    sys.linked_logins ll
        INNER JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
        INNER JOIN sys.servers s ON s.server_id = ll.server_id
WHERE   remote_name IN ('mylogin')

So I load this up in a multiple query connection and I can see which linked servers need to be updated when a database is moved from one database to another.