DBA Scripts: Generate User Mapping Script

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

The databases are moving to a new server and the users, custom roles, role memberships and database owner have to match the source, including after a restore, which hands each database to whoever ran the RESTORE. Run this on the source. For every online, writable user database it writes ALTER AUTHORIZATION for the owner, CREATE ROLE for custom roles, CREATE USER mapped to its login by SID, and ALTER ROLE ... ADD MEMBER, for you to review and run on the target. It does not carry explicit GRANT or DENY permissions, so script those separately before cutover.

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    : sysadmin, or VIEW ANY DEFINITION plus CONNECT ANY DATABASE. Without VIEW ANY
              DEFINITION, users and role memberships are left out or every mapped user is
              marked MANUAL, so the script stops with an error. Without access to every user
              database it stops with Msg 916.
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.
              A user's DEFAULT_SCHEMA is carried when it is not dbo.
              Database names go through QUOTENAME, so a name containing ] is read and
              scripted correctly instead of failing with Msg 102.
              Each section is built with STRING_AGG, not SELECT @var = @var + ... ORDER BY,
              which MS Docs calls nondeterministic. Needs SQL Server 2017 or later.
              NOT scripted, by design: explicit GRANT / DENY permissions. Script those from
              the source with Get-DatabasePermissions.sql and compare after migrating.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

-- Without VIEW ANY DEFINITION the output is incomplete with no error: other logins are invisible
-- in sys.server_principals, so every mapped user is written as a MANUAL block, and without
-- VIEW DEFINITION in a database its users, roles and memberships are simply left out.
IF HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW ANY DEFINITION') <> 1
BEGIN
    RAISERROR(N'Generate-UserMappingScript needs VIEW ANY DEFINITION (sysadmin has it) and access to every user database (CONNECT ANY DATABASE). Without it users and role memberships are left out or wrongly marked MANUAL, so the generated script would be incomplete.', 16, 1);
    RETURN;
END;

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'-- NOT included: explicit GRANT / DENY permissions. Script those separately.' + @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 ' + QUOTENAME(@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 + ISNULL(STRING_AGG(CAST(
          N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@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 AS NVARCHAR(MAX)), N'') WITHIN GROUP (ORDER BY rname), N'')
    FROM #roles;

    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), dschema NVARCHAR(128) NULL, login_name NVARCHAR(128) NULL);
    SET @q = N'SELECT name, type, authentication_type_desc, sid, default_schema_name
               FROM ' + QUOTENAME(@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, dschema) 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 + ISNULL(STRING_AGG(CAST(
          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 ' + QUOTENAME(@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)
                   + CASE WHEN dschema IS NOT NULL AND dschema <> N'dbo' THEN N' WITH DEFAULT_SCHEMA = ' + QUOTENAME(dschema) ELSE N'' END + @crlf
            -- Genuinely login-less (EXECUTE AS / impersonation user). Faithful as-is.
            WHEN auth_type = 'NONE'
                THEN N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@dbname) + N'.sys.database_principals WHERE name = N''' + REPLACE(uname, N'''', N'''''') + N''')' + @crlf
                   + N'    CREATE USER ' + QUOTENAME(uname) + N' WITHOUT LOGIN'
                   + CASE WHEN dschema IS NOT NULL AND dschema <> N'dbo' THEN N' WITH DEFAULT_SCHEMA = ' + QUOTENAME(dschema) ELSE N'' END + @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 AS NVARCHAR(MAX)), N'') WITHIN GROUP (ORDER BY uname), N'')
    FROM #users;

    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 ' + QUOTENAME(@dbname) + N'.sys.database_role_members drm
               JOIN ' + QUOTENAME(@dbname) + N'.sys.database_principals r ON drm.role_principal_id   = r.principal_id
               JOIN ' + QUOTENAME(@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 + ISNULL(STRING_AGG(CAST(
          N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@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 AS NVARCHAR(MAX)), N'') WITHIN GROUP (ORDER BY rname, mname), N'')
    FROM #rolemem;

    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

Example Output

Run it with Results to Text (Ctrl+T). The script returns its whole output as 1 long text value, and SSMS cuts text output at 256 characters per column by default, so raise that limit first: How to Increase Maximum Characters Displayed in SSMS.

The lab database's section, trimmed to 1 of 3 roles, 4 of 8 users and 2 of 20 role memberships. Every block is verbatim.

ReportingService is created for the login Lab1005_um_svc because the login is found by SID, not by name. Lab1005_um_gone lost its login on the source, so it comes out as a -- MANUAL block with its SID and nothing to run, and its role membership further down is skipped by its IF EXISTS guard. Lab1005_um_etl keeps its etl default schema. A user whose default schema is dbo gets no clause, because dbo is what CREATE USER gives it anyway.

Understanding the Results

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 Orphaned Users to catch any user whose SID did not line up with a login on the target.
• Expect -- MANUAL blocks on a real migration. They are a normal finding, not an edge case: review each one rather than assuming every user mapped cleanly.

MS Docs covers sys.database_principals, sys.server_principals, sys.databases and CREATE USER.

Frequently Asked Questions

Summary

One generator, one job: recreate the database owner, custom roles, users and role memberships across every user database on the target. Run it after the logins are recreated and the databases are restored, then check the MANUAL blocks and apply the explicit permissions, which it does not carry.

Related