Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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

-- linked servers and their logins
select, 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

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

-- linked server logins
select * 
from sys.linked_logins

-- local logins
select * 
from sys.server_principals
share|improve this answer
up vote 1 down vote accepted

And a quick trace told me the answer:


And a query to find more info:

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.