DBA Scripts: Generate Linked Server Script

July 29, 2026

–

Part of the DBA-Tools Project.

Recreating Linked Servers and Login Mappings for a Migration

Moving a SQL Server workload to a new instance means recreating every linked server and its login mappings, unless you script it. This script produces sp_addlinkedserver and sp_addlinkedsrvlogin DDL for every linked server on the source, reviewed in your own SSMS window, then run against the target once you’re satisfied it’s correct. Stored remote credentials can’t be scripted, SQL Server doesn’t expose them, so those mappings come out with an ENTER_PASSWORD_HERE placeholder and a clear comment; impersonation mappings need no manual entry.

A real bug turned up testing this against a real SQL Server 2025 instance instead of just reading it end to end. It’s fixed below, in the script and in this post.

Why a Generated Linked Server Script Matters

Linked server login mappings are easy to get subtly wrong by hand, especially the difference between impersonation and an explicit remote login mapping
Generated DDL means you review the exact sp_addlinkedserver and sp_addlinkedsrvlogin calls before they run, not a black-box migration tool doing it for you
This is a generator, not a full migration platform. It doesn’t move data or orchestrate cutover, it solves the specific problem of recreating linked server definitions correctly on the target

When to Run This Script

Migrating databases to new hardware or a new SQL Server version, on a source instance that has linked servers configured
Consolidating several instances onto one server
Standing up a DR or failover target that needs the same linked server topology as production

The Script

/*
Script Name : Generate-LinkedServerScript
Category    : migration
Purpose     : Generate sp_addlinkedserver + sp_addlinkedsrvlogin DDL for all linked servers.
              Run on SOURCE server. Execute the output on TARGET after migration.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-linked-server-script/)
Requires    : VIEW ANY DEFINITION or sysadmin
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-linked-server cursor, per-login-mapping nested cursor ...
SELECT @ddl AS ddl;

(The full script is in the repo, link below.)

How To Run From The Repo

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

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

# Set the source server for the session:
.\tools\local-sql\Set-SqlConnection.ps1 -ServerInstance PROD01\SQL2019

# Generate the DDL, review the output before running it on the target:
.\powershell\migration\Generate-LinkedServerScript.ps1

# Output: output-files\migration\*.sql

This script lives in the repo at:

sql/migration/Generate-LinkedServerScript.sql

The Bug: Queried Columns That Don’t Exist on `sys.linked_logins`

The login-mapping cursor failed immediately: Invalid column name 'local_login_name'. Invalid column name 'uses_self_credentials'. The original query assumed sys.linked_logins exposes a local_login_name column directly and a plural uses_self_credentials flag. Neither exists. The real columns are local_principal_id (an integer that has to be joined back to sys.server_principals to get a login name) and uses_self_credential, singular.

Fixed by adding the join and correcting the column name:

DECLARE ll_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        ISNULL(sp.name, N''),
        ISNULL(ll.remote_name, N''),
        ll.uses_self_credential
    FROM sys.linked_logins ll
    INNER JOIN sys.servers s2 ON ll.server_id = s2.server_id
    LEFT JOIN sys.server_principals sp ON ll.local_principal_id = sp.principal_id
    WHERE s2.name = @ls_name
    ORDER BY sp.name;

Verified by creating a real throwaway linked server (ZZZ_TEST_LINKED) with both an impersonation mapping and an explicit login mapping, dropping it, then re-running the fixed generator’s own output from scratch to recreate it. Queried sys.linked_logins afterward and confirmed both mappings came back exactly as configured, then dropped the test server.

Example Output — Verified

A real throwaway linked server, ZZZ_TEST_LINKED, created with both an impersonation mapping and an explicit login mapping. Generated DDL from the fixed script recreated it from scratch, and sys.linked_logins afterward confirmed both mappings came back exactly as configured. The test server was dropped once verified.

Understanding the Results

local_principal_id, not local_login_name, is the actual column on sys.linked_logins, any custom query against this DMV needs the join back to sys.server_principals to resolve a login name
ENTER_PASSWORD_HERE in the output is expected, not a bug, stored remote credentials genuinely cannot be read back out of SQL Server, that placeholder is the honest limit of what’s scriptable
uses_self_credential mappings need no manual entry, only explicit remote login mappings need the password placeholder filled in by hand

Best Practices

Always review the generated DDL before running it on the target. Nothing here executes automatically.
Replace every ENTER_PASSWORD_HERE placeholder in the output by hand, there’s no way to script stored remote credentials.
Run this after the target’s own logins exist, if a linked server uses an explicit login mapping, that login needs to already be recreated (see Generate Login Script).
Test connectivity through each recreated linked server after migration, a linked server that creates without error can still fail its first real query if a mapping was missed.

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why can’t remote login passwords be scripted?

SQL Server never exposes stored linked server credentials in any readable form, by design, they’re stored encrypted and only usable internally at connection time. The generator emits a clear placeholder and comment instead of silently producing broken DDL.

What’s the difference between an impersonation mapping and an explicit login mapping?

Impersonation (uses_self_credential = 1) passes the local login’s own credentials through to the remote server, no password needed. An explicit mapping connects as a specific remote login with its own stored password, which is exactly the credential that can’t be scripted and needs ENTER_PASSWORD_HERE filled in by hand.

Summary

One generator, one job: recreate every linked server and its login mappings on the target. The one real bug found testing this against a live instance, querying columns that don’t exist on sys.linked_logins, is fixed and verified with a real drop-and-recreate round trip. Replace the password placeholders by hand, that part genuinely can’t be automated.

Migration

Blog Categories

Comments

Leave a Reply Cancel reply