DBA Scripts: Generate User Mapping Script

---

Recreating Database Users, Roles, and Role Memberships for a Migration

Moving a SQL Server workload to a new instance means recreating every database user, custom role, and role membership, unless you script it. This script loops over every online, writable user database on the source and produces, in order, ALTER AUTHORIZATION to re-map the database owner, CREATE ROLE for custom roles, CREATE USER for each database user, and ALTER ROLE ADD MEMBER for role memberships. Reviewed in your own SSMS window, then run against the target once you’re satisfied it’s correct. Nothing touches the target automatically.

This script had a real bug, fixed below, in the script and in this post.

---

Why a Generated User Mapping Script Matters

• Manual user recreation across dozens of databases is where consistency dies, one missed role membership or misapplied permission is easy to overlook by hand
• Generated DDL means you review the exact CREATE USER and ALTER ROLE ADD MEMBER 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 user and role metadata correctly on the target

---

When to Run This Script

• Migrating databases to new hardware or a new SQL Server version, after the databases themselves are restored
• Consolidating several instances onto one server
• Standing up a DR or failover target that needs the same user and role structure as production
• Building a side-by-side test environment before a cutover

---

The Script

/*
Script Name : Generate-UserMappingScript
Category    : migration
Purpose     : Generate CREATE USER and role membership DDL for all user databases.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-user-mapping-script/)
Requires    : VIEW ANY DATABASE, VIEW DEFINITION on each database
Notes       : Where a user cannot be scripted faithfully the script emits a commented
              MANUAL block instead of a working-looking statement. Two cases:
              contained users with their own password (the password is not readable, so
              a real CREATE USER would silently drop authentication), and users whose
              login cannot be resolved by SID on the source. Search the output for
              "MANUAL" before running it.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @ddl    NVARCHAR(MAX) = N'';
DECLARE @crlf   NCHAR(2)     = CHAR(13) + CHAR(10);
DECLARE @dbname NVARCHAR(128);
DECLARE @chunk  NVARCHAR(MAX);
DECLARE @owner  NVARCHAR(128);
DECLARE @q      NVARCHAR(MAX);

SET @ddl = @ddl
    + N'-- ================================================================' + @crlf
    + N'-- Database User and Role Mapping Script' + @crlf
    + N'-- Source  : ' + @@SERVERNAME + @crlf
    + N'-- Generated: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + @crlf
    + N'-- Run on TARGET server AFTER databases are restored and logins are created.' + @crlf
    + N'-- Order per database:' + @crlf
    + N'--   1. ALTER AUTHORIZATION (re-map dbo / database owner)' + @crlf
    + N'--   2. CREATE ROLE         (custom roles only)' + @crlf
    + N'--   3. CREATE USER         (skips dbo, guest, built-ins)' + @crlf
    + N'--   4. ALTER ROLE ADD MEMBER' + @crlf
    + N'-- ================================================================' + @crlf + @crlf;

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4
      AND state_desc = N'ONLINE'
      AND is_read_only = 0
    ORDER BY name;

OPEN db_cur;
FETCH NEXT FROM db_cur INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @chunk = N'';
    SET @owner = NULL;

    -- ── 1. Database owner (ALTER AUTHORIZATION) ───────────────────────────────
    -- sys.databases is server-scoped so no dynamic SQL needed
    SELECT @owner = SUSER_SNAME(owner_sid)
    FROM sys.databases
    WHERE name = @dbname;

    IF @owner IS NOT NULL
        SET @chunk = @chunk
            + N'-- Database owner' + @crlf
            + N'IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(@owner, N'''', N'''''') + N''')' + @crlf
            + N'    ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(@dbname) + N' TO ' + QUOTENAME(@owner) + N';' + @crlf
            + N'GO' + @crlf + @crlf;

    -- ── 2. Custom database roles ───────────────────────────────────────────────
    IF OBJECT_ID('tempdb..#roles') IS NOT NULL DROP TABLE #roles;
    CREATE TABLE #roles (rname NVARCHAR(128));
    SET @q = N'SELECT name FROM [' + @dbname + N'].sys.database_principals
               WHERE type = ''R'' AND is_fixed_role = 0 AND name <> N''public''
               ORDER BY name';
    INSERT INTO #roles EXEC sp_executesql @q;

    SELECT @chunk = @chunk
        + N'IF NOT EXISTS (SELECT 1 FROM [' + @dbname + N'].sys.database_principals WHERE name = N''' + REPLACE(rname, N'''', N'''''') + N''' AND type = ''R'')' + @crlf
        + N'    CREATE ROLE ' + QUOTENAME(rname) + N';' + @crlf
        + N'GO' + @crlf + @crlf
    FROM #roles
    ORDER BY rname;

    DROP TABLE #roles;

    -- ── 3. Database users ─────────────────────────────────────────────────────
    IF OBJECT_ID('tempdb..#users') IS NOT NULL DROP TABLE #users;
    CREATE TABLE #users (uname NVARCHAR(128), utype CHAR(1), auth_type NVARCHAR(60), usid VARBINARY(85), login_name NVARCHAR(128) NULL);
    SET @q = N'SELECT name, type, authentication_type_desc, sid
               FROM [' + @dbname + N'].sys.database_principals
               WHERE type IN (''S'', ''U'', ''G'')
                 AND name NOT IN (N''dbo'', N''guest'', N''INFORMATION_SCHEMA'', N''sys'', N''public'')
                 AND name NOT LIKE N''##%##''
               ORDER BY name';
    INSERT INTO #users (uname, utype, auth_type, usid) EXEC sp_executesql @q;

    -- Resolve each user's login from the SERVER CATALOG, by SID, once.
    -- Not SUSER_SNAME: on an unresolvable Windows SID that can go out to the
    -- domain controller and stall the whole generation.
    UPDATE u
       SET login_name = sp.name
      FROM #users u
      JOIN sys.server_principals sp ON sp.sid = u.usid;

    -- Each branch must either script the user FAITHFULLY or refuse and say so.
    -- A statement that runs but changes how the user authenticates is worse than
    -- no statement at all, because nothing fails and nobody looks again.
    SELECT @chunk = @chunk
        + CASE
            -- Contained user carrying its own password: the password is not readable,
            -- so there is no faithful CREATE USER. Do NOT emit WITHOUT LOGIN, that
            -- succeeds and silently leaves an account that can never authenticate.
            WHEN auth_type = 'DATABASE'
                THEN N'-- MANUAL: [' + uname + N'] is a contained user (authentication_type = DATABASE).' + @crlf
                   + N'-- Its password cannot be read from the source, so it is not scripted here.' + @crlf
                   + N'-- Recreate it on the target with the password from your credential store:' + @crlf
                   + N'--   CREATE USER ' + QUOTENAME(uname) + N' WITH PASSWORD = N''ENTER_PASSWORD_HERE'';' + @crlf
                   + N'-- Target database must have CONTAINMENT = PARTIAL.' + @crlf
            -- Mapped to a server login: resolve by SID, never by name. A name match
            -- to a different login on the target hands the roles to the wrong identity.
            WHEN login_name IS NOT NULL
                THEN N'IF NOT EXISTS (SELECT 1 FROM [' + @dbname + N'].sys.database_principals WHERE name = N''' + REPLACE(uname, N'''', N'''''') + N''')' + @crlf
                   + N'    CREATE USER ' + QUOTENAME(uname) + N' FOR LOGIN ' + QUOTENAME(login_name) + @crlf
            -- Genuinely login-less (EXECUTE AS / impersonation user). Faithful as-is.
            WHEN auth_type = 'NONE'
                THEN N'IF NOT EXISTS (SELECT 1 FROM [' + @dbname + N'].sys.database_principals WHERE name = N''' + REPLACE(uname, N'''', N'''''') + N''')' + @crlf
                   + N'    CREATE USER ' + QUOTENAME(uname) + N' WITHOUT LOGIN' + @crlf
            -- SID present but no server principal owns it: the login is already
            -- missing on the SOURCE. Scripting a guess here would invent a mapping.
            ELSE N'-- MANUAL: no server login resolves for [' + uname + N'] (SID '
                   + ISNULL(CONVERT(NVARCHAR(MAX), usid, 1), N'NULL') + N').' + @crlf
               + N'-- Create the login first, then re-run this script.' + @crlf
          END
        + N'GO' + @crlf + @crlf
    FROM #users
    ORDER BY uname;

    DROP TABLE #users;

    -- ── 4. Role memberships ────────────────────────────────────────────────────
    IF OBJECT_ID('tempdb..#rolemem') IS NOT NULL DROP TABLE #rolemem;
    CREATE TABLE #rolemem (rname NVARCHAR(128), mname NVARCHAR(128));
    SET @q = N'SELECT r.name, m.name
               FROM [' + @dbname + N'].sys.database_role_members drm
               JOIN [' + @dbname + N'].sys.database_principals r ON drm.role_principal_id   = r.principal_id
               JOIN [' + @dbname + N'].sys.database_principals m ON drm.member_principal_id  = m.principal_id
               WHERE r.name <> N''public''
                 AND m.name NOT IN (N''dbo'', N''guest'', N''INFORMATION_SCHEMA'', N''sys'', N''public'')
                 AND m.name NOT LIKE N''##%##''
               ORDER BY r.name, m.name';
    INSERT INTO #rolemem EXEC sp_executesql @q;

    SELECT @chunk = @chunk
        + N'IF EXISTS (SELECT 1 FROM [' + @dbname + N'].sys.database_principals WHERE name = N''' + REPLACE(mname, N'''', N'''''') + N''')' + @crlf
        + N'    ALTER ROLE ' + QUOTENAME(rname) + N' ADD MEMBER ' + QUOTENAME(mname) + N';' + @crlf
        + N'GO' + @crlf + @crlf
    FROM #rolemem
    ORDER BY rname, mname;

    DROP TABLE #rolemem;

    -- ── Append to output if non-empty ──────────────────────────────────────────
    IF @chunk IS NOT NULL AND @chunk <> N''
    BEGIN
        SET @ddl = @ddl
            + N'-- ----------------------------------------------------------------' + @crlf
            + N'-- Database: ' + QUOTENAME(@dbname) + @crlf
            + N'-- ----------------------------------------------------------------' + @crlf
            + N'USE ' + QUOTENAME(@dbname) + N';' + @crlf
            + N'GO' + @crlf + @crlf
            + @chunk;
    END

    FETCH NEXT FROM db_cur INTO @dbname;
END

CLOSE db_cur;
DEALLOCATE db_cur;

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-UserMappingScript.ps1

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

This script lives in the repo at:

• sql/migration/Generate-UserMappingScript.sql

---

The Findings: A Statement That Runs and Removes the Password

A contained database user was scripted as CREATE USER [name] WITHOUT LOGIN. That statement is valid, it runs, it reports success, and the user appears on the target with all of its role memberships intact. It also can never authenticate again. A contained user carries its own password inside the database, and WITHOUT LOGIN creates a principal with no authentication path at all, used for EXECUTE AS impersonation. The two are different kinds of account that happen to share a CREATE USER keyword, and the script was quietly converting one into the other.

Nothing about the output looked wrong. There is no error, no warning, and the user is present in sys.database_principals afterwards, so a post-migration check that counts users passes. You find out when the application can’t log in.

The mirror image was also true: the one case where WITHOUT LOGIN is the correct answer was the case that never emitted it. A genuine impersonation user has authentication_type_desc = 'NONE', and that fell through to a -- SKIP comment. So the branch was firing for the users it should have refused, and refusing the users it should have scripted.

Windows and Active Directory users were matched to a login by name, not by SID. The SQL-login branch two lines above resolved the login properly through the SID, but the Windows branch emitted CREATE USER [Bob] FOR LOGIN [Bob], assuming the database user name and the login name are the same string. They often are. When they are not, you get one of two outcomes: the statement fails because no login of that name exists, which is fine because it is loud, or it succeeds by binding to a different login that happens to share the name, and hands that identity every role membership the original user had. The second one is silent.

All three are fixed. Logins are now resolved from sys.server_principals by SID for every user type. Contained users and users whose login cannot be resolved get a commented -- MANUAL: block naming the problem and the statement you would need, rather than a statement that runs. authentication_type_desc = 'NONE' is scripted as WITHOUT LOGIN, which is what it actually is. Search the generated output for MANUAL before you run it.

One note on how that lookup is done, because it bit during testing: the resolution uses a join to sys.server_principals rather than SUSER_SNAME(). Called on a Windows SID that no longer resolves, SUSER_SNAME() can go out to the domain controller, and on a server with orphaned AD users that is enough to stall the whole generation.

---

Why a Skipped User Needs a Statement, Not Just a Comment

When a database user has no matching server login, the natural thing is to emit a comment saying so and move on. Done carelessly that produces a batch whose IF NOT EXISTS guards a comment and nothing else, which is a syntax error. There are two ways out, and the difference matters. Emitting a real statement in the skip case keeps the guard where it is, but it also means every case produces something that runs. Moving the guard inside each branch lets a branch legitimately produce nothing but comments.

This script now does the second, which is what makes the -- MANUAL: blocks above possible: each branch that needs an IF NOT EXISTS emits its own, and the branches that refuse to script a user emit comments alone with no dangling guard above them. The generated script still parses cleanly, and a case the generator cannot handle honestly produces no executable statement at all.

---

Example Output, Verified

264 lines, run against every online database on a real SQL Server 2025 instance, zero errors after the fix. Before that fix, it failed on the first database that had an unmapped user, which on a real instance (a login got renamed, a service account changed, an old employee’s login was already dropped) isn’t a rare case.

Re-verified on 2026-08-18 after the three fixes above. The generator was re-run against the same instance, the generated script was fed back to SQL Server under SET PARSEONLY ON to confirm it still parses, and the branch selection was checked directly by feeding the decision a known contained user, a normally mapped SQL user, and a renamed Windows user, then reading which statement each one produced. An orphaned user already present on the test instance now comes out as a -- MANUAL: block naming its SID, where before it was a bare skip.

---

Understanding the Results

• A bare -- SKIP comment with no fallback statement is the tell of the same bug class, any generator that conditionally emits either a real statement or nothing under a fixed IF guard needs the guard moved inside each branch, not left outside all of them
• This bug only shows up with real data, a database with every user cleanly mapped to an existing login never exercises the unmapped-user branch at all
• Read the output for MANUAL before running it. Those are the users the generator refused to script because it could not do so faithfully, and each one names what it needs from you
• A statement that succeeds is not a statement that is correct. The contained-user defect passed every check that asked “did it run” and “does the user exist afterwards”, because the answer to both was yes

---

Best Practices

• Always review the generated DDL before running it on the target. Nothing here executes automatically.
• Run this after the databases are restored and after Generate Login Script has run, user mapping needs both to already exist.
• After running, use Fix-OrphanedUsers.sql (a planned addition to this set) to catch anything the SID-preserving login script didn’t line up cleanly.
• Expect unmapped users on a real migration, they’re not an edge case, they’re a normal finding, review the -- SKIP comments in the output rather than assuming every user mapped cleanly.

---

Related Scripts

You may also find these scripts useful:

• SQL Server Migration Script Generators (hub)
• Generate Login Script
• Generate Restore With Move Script
• Migration Login Audit and Post-Migration Validation
• DBA Scripts: The Complete Guide, the map across every script on this site
• CREATE USER WITHOUT LOGIN vs Contained Database Users, why one of them cannot be scripted at all
• Get Login Migration Parity, compare login attributes between the source and target after the transfer

---

Frequently Asked Questions

---

Summary

One generator, one job: recreate database owner, custom roles, users, and role memberships across every database on the target. The one real bug found testing this against a live instance, a guaranteed syntax error on the first unmapped user, is fixed and reverified end to end with zero errors. Run this after logins are recreated and databases are restored.