DBA Scripts: Generate Linked Server Script

---

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.

This script had a real bug, 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;

/*
  DESIGN: Linked server login mappings with stored remote credentials cannot have passwords
  scripted — SQL Server does not expose them. Mappings using stored credentials are scripted
  with a placeholder (NULL password) and a clear comment. Re-enter the remote password manually
  on the target for each HIGH-risk mapping flagged by Get-LinkedServerSecurity.sql.

  Impersonation mappings (useself = 1) are scripted correctly and require no manual password entry.
  No-mapping entries (access denied for unmatched logins) are scripted correctly.
*/

DECLARE @ddl  NVARCHAR(MAX) = N'';
DECLARE @crlf NCHAR(2)      = CHAR(13) + CHAR(10);

SET @ddl = @ddl
    + N'-- ================================================================' + @crlf
    + N'-- Linked Server Migration Script' + @crlf
    + N'-- Source  : ' + @@SERVERNAME + @crlf
    + N'-- Generated: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + @crlf
    + N'-- IMPORTANT: Stored credentials (remote_login / rmtpassword) cannot' + @crlf
    + N'-- be scripted. Lines marked ENTER_PASSWORD_HERE require manual entry.' + @crlf
    + N'-- Run Get-LinkedServerSecurity.sql to identify HIGH-risk mappings.' + @crlf
    + N'-- ================================================================' + @crlf + @crlf;

-- ── One block per linked server ───────────────────────────────────────────────

DECLARE @ls_name      NVARCHAR(128);
DECLARE @ls_product   NVARCHAR(128);
DECLARE @ls_provider  NVARCHAR(128);
DECLARE @ls_datasrc   NVARCHAR(4000);
DECLARE @ls_location  NVARCHAR(4000);
DECLARE @ls_provstr   NVARCHAR(4000);
DECLARE @ls_catalog   NVARCHAR(128);
DECLARE @ls_rpc_out   bit;
DECLARE @ls_collation bit;

DECLARE ls_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        s.name,
        ISNULL(s.product,   N''),
        ISNULL(s.provider,  N'SQLNCLI'),
        ISNULL(s.data_source, N''),
        ISNULL(CAST(s.location AS NVARCHAR(4000)), N''),
        ISNULL(CAST(s.provider_string AS NVARCHAR(4000)), N''),
        ISNULL(s.catalog,   N''),
        s.is_rpc_out_enabled,
        s.is_collation_compatible
    FROM sys.servers s
    WHERE s.is_linked = 1
    ORDER BY s.name;

OPEN ls_cur;
FETCH NEXT FROM ls_cur INTO
    @ls_name, @ls_product, @ls_provider, @ls_datasrc,
    @ls_location, @ls_provstr, @ls_catalog, @ls_rpc_out, @ls_collation;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ddl = @ddl
        + N'-- Linked Server: ' + @ls_name + @crlf
        + N'IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = N''' + REPLACE(@ls_name, N'''', N'''''') + N''' AND is_linked = 1)' + @crlf
        + N'BEGIN' + @crlf
        + N'    EXEC sp_addlinkedserver' + @crlf
        + N'        @server     = N''' + REPLACE(@ls_name,     N'''', N'''''') + N''',' + @crlf
        + N'        @srvproduct = N''' + REPLACE(@ls_product,  N'''', N'''''') + N''',' + @crlf
        + N'        @provider   = N''' + REPLACE(@ls_provider, N'''', N'''''') + N''',' + @crlf
        + N'        @datasrc    = N''' + REPLACE(@ls_datasrc,  N'''', N'''''') + N'''' + @crlf
        + CASE WHEN @ls_location <> N''
               THEN N'       ,@location  = N''' + REPLACE(@ls_location, N'''', N'''''') + N'''' + @crlf
               ELSE N'' END
        + CASE WHEN @ls_provstr <> N''
               THEN N'       ,@provstr   = N''' + REPLACE(@ls_provstr,  N'''', N'''''') + N'''' + @crlf
               ELSE N'' END
        + CASE WHEN @ls_catalog <> N''
               THEN N'       ,@catalog   = N''' + REPLACE(@ls_catalog,  N'''', N'''''') + N'''' + @crlf
               ELSE N'' END
        + N';' + @crlf + @crlf;

    -- Options
    IF @ls_rpc_out = 1
        SET @ddl = @ddl
            + N'    EXEC sp_serveroption @server = N''' + REPLACE(@ls_name, N'''', N'''''') + N''', @optname = N''rpc out'', @optvalue = N''true'';' + @crlf;

    IF @ls_collation = 1
        SET @ddl = @ddl
            + N'    EXEC sp_serveroption @server = N''' + REPLACE(@ls_name, N'''', N'''''') + N''', @optname = N''collation compatible'', @optvalue = N''true'';' + @crlf;

    -- Login mappings for this linked server
    DECLARE @ll_local   NVARCHAR(128);
    DECLARE @ll_remote  NVARCHAR(128);
    DECLARE @ll_useself bit;

    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;

    OPEN ll_cur;
    FETCH NEXT FROM ll_cur INTO @ll_local, @ll_remote, @ll_useself;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ddl = @ddl + @crlf
            + CASE
                WHEN @ll_useself = 1 THEN
                    N'    -- Impersonation mapping (self credentials)' + @crlf +
                    N'    EXEC sp_addlinkedsrvlogin' + @crlf +
                    N'        @rmtsrvname  = N''' + REPLACE(@ls_name,  N'''', N'''''') + N''',' + @crlf +
                    N'        @useself     = N''True''' + @crlf +
                    CASE WHEN @ll_local <> N''
                         THEN N'       ,@locallogin = N''' + REPLACE(@ll_local, N'''', N'''''') + N'''' + @crlf
                         ELSE N'' END +
                    N'    ;' + @crlf
                WHEN @ll_remote <> N'' AND @ll_local = N'' THEN
                    N'    -- Catch-all mapping — ENTER_PASSWORD_HERE (stored credential, cannot be scripted)' + @crlf +
                    N'    EXEC sp_addlinkedsrvlogin' + @crlf +
                    N'        @rmtsrvname  = N''' + REPLACE(@ls_name,   N'''', N'''''') + N''',' + @crlf +
                    N'        @useself     = N''False'',' + @crlf +
                    N'        @locallogin  = NULL,' + @crlf +
                    N'        @rmtuser     = N''' + REPLACE(@ll_remote, N'''', N'''''') + N''',' + @crlf +
                    N'        @rmtpassword = N''ENTER_PASSWORD_HERE'';' + @crlf
                WHEN @ll_remote <> N'' THEN
                    N'    -- Explicit mapping — ENTER_PASSWORD_HERE (stored credential, cannot be scripted)' + @crlf +
                    N'    EXEC sp_addlinkedsrvlogin' + @crlf +
                    N'        @rmtsrvname  = N''' + REPLACE(@ls_name,   N'''', N'''''') + N''',' + @crlf +
                    N'        @useself     = N''False'',' + @crlf +
                    N'        @locallogin  = N''' + REPLACE(@ll_local,  N'''', N'''''') + N''',' + @crlf +
                    N'        @rmtuser     = N''' + REPLACE(@ll_remote, N'''', N'''''') + N''',' + @crlf +
                    N'        @rmtpassword = N''ENTER_PASSWORD_HERE'';' + @crlf
                ELSE
                    N'    -- No mapping (access denied for unmatched logins)' + @crlf +
                    N'    EXEC sp_addlinkedsrvlogin' + @crlf +
                    N'        @rmtsrvname  = N''' + REPLACE(@ls_name,  N'''', N'''''') + N''',' + @crlf +
                    N'        @useself     = N''False'',' + @crlf +
                    N'        @locallogin  = NULL,' + @crlf +
                    N'        @rmtuser     = NULL,' + @crlf +
                    N'        @rmtpassword = NULL;' + @crlf
              END;

        FETCH NEXT FROM ll_cur INTO @ll_local, @ll_remote, @ll_useself;
    END

    CLOSE ll_cur;
    DEALLOCATE ll_cur;

    SET @ddl = @ddl + N'END' + @crlf + N'GO' + @crlf + @crlf;

    FETCH NEXT FROM ls_cur INTO
        @ls_name, @ls_product, @ls_provider, @ls_datasrc,
        @ls_location, @ls_provstr, @ls_catalog, @ls_rpc_out, @ls_collation;
END

CLOSE ls_cur;
DEALLOCATE ls_cur;

IF @ddl = N''
    SET @ddl = N'-- No linked servers found on ' + @@SERVERNAME + CHAR(13) + CHAR(10);

SELECT @ddl AS ddl;

---

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

---

What sys.linked_logins Actually Exposes

Scripting linked server logins means reading sys.linked_logins, and it does not expose the columns people expect. There is no local_login_name and no uses_self_credentials. The mapping lives in local_principal_id (zero meaning it applies to everyone) and uses_self_credential, singular. Join to sys.server_principals to turn that id into a login name.

---

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:

• SQL Server Migration Script Generators (hub)
• Generate Login Script
• Generate Agent Job Script
• Get Linked Servers
• Fix “Msg 207: Invalid Column Name”
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

---

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.