DBA Scripts: Generate User Mapping Script

July 29, 2026

–

Part of the DBA-Tools Project.

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.

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 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
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ... per-database dynamic SQL assembling owner/role/user/membership DDL ...
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-UserMappingScript.ps1

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

This script lives in the repo at:

sql/migration/Generate-UserMappingScript.sql

The Bug: A Bare Comment With No Statement, a Guaranteed Syntax Error the Moment Any User Has No Matching Login

For a database user with no server-level login to map to, the generator emitted:

IF NOT EXISTS (SELECT 1 FROM [DemoDatabase].sys.database_principals WHERE name = N'DemoSeverityUser')
    -- SKIP (no matching server login): DemoSeverityUser
GO

A comment isn’t a statement. IF with nothing but a comment after it is Incorrect syntax near ')', Msg 102, confirmed by running that exact pattern directly. The bug: the generator’s CASE expression built the IF NOT EXISTS guard as a fixed prefix, then appended the CASE result (either a real CREATE USER statement or, in the unmapped case, just a bare comment) underneath it, unconditionally, on every branch. Unmapped users are common in a real migration (a login got renamed, a service account changed, an old employee’s login was already dropped), so this wasn’t a rare edge case, it broke on the very first unmapped user in a real run.

Fixed by moving the IF NOT EXISTS guard inside the three branches that actually need it, and letting the unmapped case stand alone as a plain comment with no guard at all:

SELECT @chunk = @chunk
    + CASE
        WHEN utype IN ('U', 'G')
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] FOR LOGIN [...]' + @crlf
        WHEN utype = 'S' AND usid IS NOT NULL AND EXISTS (...)
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] FOR LOGIN [...]' + @crlf
        WHEN utype = 'S' AND auth_type = 'DATABASE'
            THEN N'IF NOT EXISTS (...)' + @crlf + N'    CREATE USER [...] WITHOUT LOGIN' + @crlf
        ELSE N'-- SKIP (no matching server login): ' + uname + @crlf
      END
    + N'GO' + @crlf + @crlf

Verified by re-running the generator against every real database on the test instance and executing the full 264-line output end to end with zero errors, where the previous version failed on the first database with an unmapped user.

Example Output — Verified

264 lines, run against every online database on a real SQL Server 2025 instance, zero errors after the fix. Before the 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.

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

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:

Frequently Asked Questions

Why did some databases fail with “Incorrect syntax near ‘)’” before the fix?

Any database with at least one user that had no matching server login hit a branch of the generator that produced an IF statement with only a comment underneath it, no actual T-SQL statement, which is a syntax error. Databases where every user mapped cleanly to an existing login never hit that branch, so the bug stayed hidden until tested against real, messier data.

What does an unmapped user in the output mean?

The database has a user with no corresponding server-level login on the source, common after a login rename, a service account change, or an old employee’s login being dropped while the database user remained. The generator now emits a plain -- SKIP comment for these instead of failing, review them by hand.

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.

Migration

Blog Categories

Comments

Leave a Reply Cancel reply