patternsqlMinor

Query Linked Server Logins

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

serverquerylinkedlogins

Problem

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.

Solution

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

Code Snippets

-- 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

-- 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

Context

StackExchange Database Administrators Q#29452, answer score: 7

Revisions (0)

No revisions yet.