Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

2 Answers 2

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
share|improve this answer
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

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.