DBA Scripts: Get Linked Servers

Peter Whyte

–

Part of the DBA-Tools Project.

Four Questions, One Topic: What’s Linked to This Server

Linked servers are the kind of configuration that gets set up once, for one integration, and then forgotten until a migration or an incident forces someone to ask what’s actually connected to this instance and how. There are four separate questions worth asking, and one script for each:

  • What linked servers exist? — a plain inventory
  • What do they connect alongside logins and jobs, for a migration review? — a broader pre-migration snapshot
  • How risky is the security context of each one? — catch-all credential mappings are the highest-risk configuration
  • Are they actually reachable right now? — a live connectivity test, not just a config listing

This post covers all four, one script each, because they answer related but genuinely different questions and a DBA reaching for “linked servers” could mean any of them.

Why Linked Server Auditing Matters

  • A linked server with a catch-all credential mapping (any local login maps to one stored remote identity) is a real security exposure, anyone who can query the linked server inherits that remote identity
  • Before a migration, linked servers are dependencies that need to exist on the target server too, missing one breaks whatever process depends on it, often silently until that process runs
  • A linked server can be configured but unreachable, DNS changes, firewall rules, and decommissioned remote servers all leave stale linked server definitions behind
  • sys.linked_servers was removed in SQL Server 2025, scripts written against it will break; all four scripts here use sys.servers WHERE is_linked = 1 instead

When to Run These Scripts

  • Get-LinkedServerInventory — a quick “what’s linked here” check, first thing when reviewing an unfamiliar server
  • Get-LinkedServerAndJobInventory — before a migration, alongside a login and job audit, to see the full pre-migration dependency picture in one run
  • Get-LinkedServerSecurity — a periodic security review, or immediately after inheriting a server, to check for catch-all credential mappings
  • Get-LinkedServerConnectivity — when troubleshooting a process that depends on a linked server, or as a pre-migration sanity check that every linked server the target needs is actually reachable

The Scripts

1. Get-LinkedServerInventory — Plain Inventory

/*
Script Name : Get-LinkedServerInventory
Category    : migration
Purpose     : Inventory linked servers for migration and connectivity dependency mapping.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-linked-servers/)
Requires    : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-linked-servers/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
    s.name AS linked_server_name,
    s.product,
    s.provider,
    s.data_source,
    s.location,
    s.catalog,
    CASE WHEN s.is_linked = 1 THEN 'Linked' ELSE 'Local' END AS status
FROM sys.servers AS s
WHERE s.is_linked = 1
ORDER BY s.name;

2. Get-LinkedServerAndJobInventory — Migration Pre-Check (Logins + Linked Servers + Jobs)

/*
Script Name : Get-LinkedServerAndJobInventory
Category    : configuration-and-environment
Purpose     : Inventory logins, linked servers, and SQL Agent jobs for pre-migration reviews.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-linked-servers/)
Requires    : VIEW ANY DATABASE, db_datareader on msdb
Notes       : Returns three result sets (logins, linked servers, jobs). Run in SSMS or
              use the individual focused scripts for CSV export.
*/
-- Blog: https://sqldba.blog/dba-scripts-get-linked-servers/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    'LOGIN'           AS object_type,
    sp.name           AS name,
    sp.type_desc      AS detail,
    CAST(sp.is_disabled AS VARCHAR(5)) AS status
FROM sys.server_principals AS sp
WHERE sp.type IN ('S', 'U', 'G')
  AND sp.name NOT LIKE '##%'
  AND sp.name NOT LIKE 'NT AUTHORITY%'
  AND sp.name NOT LIKE 'NT SERVICE%'
ORDER BY sp.name;

SELECT
    'LINKED SERVER'           AS object_type,
    s.name                    AS name,
    s.product + ' / ' + s.provider AS detail,
    s.data_source             AS status
FROM sys.servers AS s
WHERE s.is_linked = 1
ORDER BY s.name;

SELECT
    'JOB'                     AS object_type,
    j.name                    AS name,
    ISNULL(sp.name, '(unknown)') AS detail,
    CASE j.enabled WHEN 1 THEN 'Enabled' ELSE 'Disabled' END AS status
FROM msdb.dbo.sysjobs          AS j
LEFT JOIN sys.server_principals AS sp ON j.owner_sid = sp.sid
ORDER BY j.name;

This one returns three result sets in a single run, logins, linked servers, and jobs, because those three together are what a pre-migration review actually needs: who can log in, what’s linked, and what’s scheduled to run. Run it in SSMS, or use the three focused scripts individually for clean CSV export.

3. Get-LinkedServerSecurity — Security Context and Risk Level

/*
Script Name : Get-LinkedServerSecurity
Category    : security
Purpose     : Lists linked servers with their security context — how local logins are
              mapped to remote logins. Catch-all mappings with stored credentials are
              the highest-risk configuration.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-linked-servers/)
Requires    : VIEW ANY DEFINITION or sysadmin
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-linked-servers/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: sys.linked_logins contains one row per mapping. A NULL local_login_name
  means the mapping applies to any login without a specific mapping (catch-all).
  Risk assessment:
    HIGH   — catch-all or specific mapping with stored remote credentials
    MEDIUM — self-credentials (impersonation) mapping
    LOW    — no mapping / will fail for unmatched logins
*/

SELECT
    s.name                                              AS linked_server,
    s.product,
    s.provider,
    s.data_source,
    ISNULL(s.catalog, '')                               AS remote_catalog,
    ISNULL(SUSER_SNAME(NULLIF(ll.local_principal_id, 0)), '(any login)')  AS local_login,
    CASE
        WHEN ll.uses_self_credential = 1
            THEN 'Impersonate — context of the calling login'
        WHEN ll.remote_name IS NOT NULL AND ll.local_principal_id = 0
            THEN 'Catch-all mapping → ' + ll.remote_name
        WHEN ll.remote_name IS NOT NULL
            THEN 'Explicit mapping → ' + ll.remote_name
        ELSE '(no mapping — will fail for unmatched logins)'
    END                                                 AS security_context,
    ll.uses_self_credential,
    ll.remote_name                                      AS remote_login,
    CASE
        WHEN ll.remote_name IS NOT NULL AND ll.local_principal_id = 0
            THEN 'HIGH — catch-all with stored credentials'
        WHEN ll.remote_name IS NOT NULL AND ll.local_principal_id != 0
            THEN 'HIGH — explicit mapping with stored credentials'
        WHEN ll.uses_self_credential = 1
            THEN 'MEDIUM — impersonation (caller context)'
        WHEN ll.remote_name IS NULL AND ll.uses_self_credential = 0
            THEN 'LOW — no mapping (access denied for unmatched logins)'
        ELSE 'UNKNOWN'
    END                                                 AS risk_level,
    s.is_remote_login_enabled,
    s.is_rpc_out_enabled,
    s.modify_date                                       AS last_modified
FROM sys.servers s
LEFT JOIN sys.linked_logins ll ON ll.server_id = s.server_id
WHERE s.is_linked = 1
ORDER BY
    CASE
        WHEN ll.remote_name IS NOT NULL AND ll.local_principal_id = 0 THEN 1
        WHEN ll.remote_name IS NOT NULL                               THEN 2
        WHEN ll.uses_self_credential = 1                              THEN 3
        ELSE 4
    END,
    s.name,
    ll.local_principal_id;

This is the one that matters most for security review. A catch-all mapping (local_principal_id = 0) with stored credentials means any login on this server that doesn’t have its own explicit mapping falls through to one shared remote identity, that’s the highest-risk configuration this script flags.

4. Get-LinkedServerConnectivity — Live Connectivity Test

/*
Script Name : Get-LinkedServerConnectivity
Category    : monitoring
Purpose     : Inventories all linked servers and tests each one for connectivity using sp_testlinkedserver.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-linked-servers/)
Requires    : sysadmin or ALTER ANY LINKED SERVER
*/
-- Blog: https://sqldba.blog/dba-scripts-get-linked-servers/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/* Note: sys.linked_servers was removed in SQL Server 2025 — use sys.servers WHERE is_linked = 1 */

IF OBJECT_ID('tempdb..#LSResults') IS NOT NULL DROP TABLE #LSResults;
CREATE TABLE #LSResults (
    linked_server_name      NVARCHAR(128) NOT NULL,
    product                 NVARCHAR(128),
    provider                NVARCHAR(128),
    data_source             NVARCHAR(4000),
    is_rpc_out_enabled      BIT,
    is_remote_login_enabled BIT,
    modify_date             DATETIME,
    connectivity            VARCHAR(15)   NOT NULL DEFAULT 'UNTESTED',
    error_detail            NVARCHAR(2000)
);

INSERT INTO #LSResults (linked_server_name, product, provider, data_source,
                        is_rpc_out_enabled, is_remote_login_enabled, modify_date)
SELECT
    name,
    product,
    provider,
    data_source,
    is_rpc_out_enabled,
    is_remote_login_enabled,
    modify_date
FROM sys.servers
WHERE is_linked = 1;

/* ── Test connectivity for each linked server ────────────────────────────── */
DECLARE @ls  NVARCHAR(128);
DECLARE @err NVARCHAR(2000);

DECLARE ls_cur CURSOR FAST_FORWARD FOR
    SELECT linked_server_name FROM #LSResults ORDER BY linked_server_name;

OPEN ls_cur; FETCH NEXT FROM ls_cur INTO @ls;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC sp_testlinkedserver @ls;
        UPDATE #LSResults SET connectivity = 'REACHABLE' WHERE linked_server_name = @ls;
    END TRY
    BEGIN CATCH
        SET @err = LEFT(ERROR_MESSAGE(), 2000);
        UPDATE #LSResults
        SET connectivity = 'UNREACHABLE', error_detail = @err
        WHERE linked_server_name = @ls;
    END CATCH;
    FETCH NEXT FROM ls_cur INTO @ls;
END;
CLOSE ls_cur; DEALLOCATE ls_cur;

SELECT
    linked_server_name,
    product,
    provider,
    data_source,
    connectivity,
    is_rpc_out_enabled,
    is_remote_login_enabled,
    modify_date,
    error_detail
FROM #LSResults
ORDER BY CASE connectivity WHEN 'UNREACHABLE' THEN 1 WHEN 'REACHABLE' THEN 2 ELSE 3 END,
         linked_server_name;

DROP TABLE #LSResults;

This one actually calls sp_testlinkedserver for each linked server found, wrapped in TRY/CATCH so one unreachable server doesn’t stop the rest from being tested. error_detail captures the real connection failure message, useful for handing straight to a network or firewall investigation.

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Plain linked server inventory:
.\run.ps1 Get-LinkedServerInventory

# Migration pre-check: logins + linked servers + jobs:
.\run.ps1 Get-LinkedServerAndJobInventory

# Security context and risk level for each linked server:
.\run.ps1 Get-LinkedServerSecurity

# Live connectivity test:
.\run.ps1 Get-LinkedServerConnectivity

# Any of these against a remote server:
.\run.ps1 Get-LinkedServerSecurity -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

Real output from this lab instance, not staged. This lab box has no linked servers configured, so Get-LinkedServerInventory, Get-LinkedServerSecurity, and Get-LinkedServerConnectivity all correctly returned zero rows, an honest “nothing found” result, not a bug. That’s a valid and useful finding in its own right: it confirms there’s no linked-server dependency to carry into a migration plan for this server.

Get-LinkedServerAndJobInventory still returns real data for its login and job result sets (condensed here, 11 logins and 5 jobs total on this instance):

LOGIN result set (sample):

object_type name detail status
LOGIN DBA_ServiceAccount SQL_LOGIN 0
LOGIN DemoDisabledAdmin SQL_LOGIN 1
LOGIN HPAI01\Peter WINDOWS_LOGIN 0
LOGIN sa SQL_LOGIN 0

LINKED SERVER result set: (empty, none configured)

JOB result set:

object_type name detail status
JOB DBA Daily Full Backups sa Enabled
JOB DBA Index Stats Maintenance sa Enabled
JOB DBA Watchtower CollectDatabaseGrowthEvents sa Enabled
JOB DBA Watchtower Database Free Space Monitor sa Enabled
JOB syspolicy_purge_history sa Enabled

status = 1 on DemoDisabledAdmin in the login result set is exactly the kind of thing this script is built to surface fast, a disabled login sitting in the list, worth a second look during any migration or access review.

Understanding the Results

  • Zero linked servers is a valid finding — don’t mistake an empty result set for a script failure, run Get-LinkedServerInventory alone first to sanity-check before assuming something’s broken
  • is_linked = 1 filter — sys.servers also contains the local server itself and any non-linked entries, all four scripts filter to only genuinely linked servers
  • risk_level HIGH — a catch-all or explicit mapping with stored remote credentials, this is the finding to act on first if present
  • connectivity = UNREACHABLE — check error_detail for the real connection error, common causes are DNS resolution, firewall rules, or a decommissioned remote server left behind in configuration

Best Practices

  • Run Get-LinkedServerInventory first as a quick sanity check, then reach for the security or connectivity scripts only if linked servers actually exist
  • Treat any catch-all credential mapping (risk_level = HIGH) as a finding to resolve, not just document, scope the mapping to specific logins or remove the stored credential entirely
  • Before a migration, run Get-LinkedServerAndJobInventory alongside a full login and job audit, missing a linked server dependency on the target is a common, avoidable migration gap
  • Re-run Get-LinkedServerConnectivity after any network change (firewall rule, DNS update, remote server maintenance) that could affect a linked server’s reachability

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why four scripts instead of one combined script?

Each answers a genuinely different question, plain inventory, migration pre-check, security risk, and live connectivity are different enough in scope and permission requirements (Get-LinkedServerConnectivity needs sysadmin or ALTER ANY LINKED SERVER, the others don’t) that combining them into one script would mean over-requesting permissions or silently skipping sections.

What replaced sys.linked_servers in SQL Server 2025?

sys.servers WHERE is_linked = 1. All four scripts here already use it, if you have older scripts referencing sys.linked_servers directly, they’ll break on SQL Server 2025 and need the same fix.

Does a catch-all mapping always mean a problem?

Not automatically, some environments deliberately use one shared service account for simplicity. But it does mean anyone with access to run a query through that linked server inherits that identity’s full permissions on the remote side, worth confirming it’s an intentional choice, not a forgotten default.

Summary

Linked servers are easy to forget about until a migration or a security review forces the question. These four scripts cover the question from every angle that matters: what’s linked, what else is tied to it for migration purposes, how risky the credential mapping is, and whether it’s actually reachable right now.

Run Get-LinkedServerInventory as a first pass on any server, and reach for the security and connectivity scripts specifically when planning a migration or investigating an access review finding.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *