DBA Scripts: Generate Login Script

---

SID-Preserving Login Recreation for a Migration

Moving a SQL Server workload to a new instance means recreating every login, unless you script it, and doing it wrong means restored databases end up with orphaned users the moment a login’s SID doesn’t match the original database user’s SID. This script reads the source instance’s own system catalogs and produces plain CREATE LOGIN DDL text, including the SQL logins’ password hashes and original SIDs, 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 two real defects, both fixed below, in the script and in this post. One of them meant it silently skipped every Windows login on the server. The other quietly re-enabled accounts that were disabled on the source. Neither one produced an error message.

---

Why SID-Preserving Login Recreation Matters

• A CREATE USER mapped to a login whose SID doesn’t match the original database user’s SID silently fails to map, and you find out when an application can’t connect, not at migration time
• Manual login recreation across a maintenance window is exactly when one gets missed, or a permission gets applied differently than the one before it
• Generated DDL means you review the exact CREATE LOGIN statement before it runs, 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 login identity correctly on the target

---

When to Run This Script

• Migrating databases to new hardware or a new SQL Server version
• Consolidating several instances onto one server
• Standing up a DR or failover target that needs the same logins as production
• Building a side-by-side test environment before a cutover

---

The Script

Produces CREATE LOGIN DDL for every non-system SQL and Windows login, including the SQL logins’ password hashes and original SIDs, plus server role memberships. Preserving the SID is what prevents orphaned database users after the databases are restored. The excerpt below shows the two corrected sections rather than the whole assembly.

/*
Script Name : Generate-LoginScript
Category    : migration
Purpose     : Generate CREATE LOGIN DDL for all non-system logins with SIDs and hashed passwords preserved.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-generate-login-script/)
Requires    : VIEW SERVER STATE, CONTROL SERVER (for password_hash column)
Notes       : Disabled logins are re-created and then DISABLED again. CREATE LOGIN always
              produces an enabled login, so the state has to be re-applied explicitly.
              Without it a decommissioned account comes back live on the target.
              NOT scripted, by design: server-level permissions and explicit DENY CONNECT SQL.
              Run Get-MigrationLoginAudit.sql on the source and compare after migrating.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @ddl  NVARCHAR(MAX) = N'';
DECLARE @crlf NCHAR(2)      = CHAR(13) + CHAR(10);
DECLARE @len  INT;   -- section length marker: an empty section must SAY it is empty

SET @ddl = @ddl
    + N'-- ================================================================' + @crlf
    + N'-- Login Migration Script' + @crlf
    + N'-- Source  : ' + @@SERVERNAME + @crlf
    + N'-- Generated: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + @crlf
    + N'-- Run on TARGET server AFTER databases are restored.' + @crlf
    + N'-- SQL logins include hashed passwords and original SIDs to avoid' + @crlf
    + N'-- orphaned users after restore.' + @crlf
    + N'-- NOTE: If a login''s DEFAULT_DATABASE does not exist on the target,' + @crlf
    + N'-- the login will fail to connect. Fix with:' + @crlf
    + N'--   ALTER LOGIN [name] WITH DEFAULT_DATABASE = [master]' + @crlf
    + N'-- NOTE: Logins disabled on the source are re-disabled below. Server-level' + @crlf
    + N'-- permissions and explicit DENY CONNECT SQL are NOT scripted, audit those' + @crlf
    + N'-- separately with Get-MigrationLoginAudit.sql.' + @crlf
    + N'-- ================================================================' + @crlf + @crlf;

-- ── SQL logins ────────────────────────────────────────────────────────────────

SET @ddl = @ddl + N'-- SQL Logins' + @crlf + N'GO' + @crlf + @crlf;
SET @len = LEN(@ddl);


SELECT @ddl = @ddl
    + N'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(p.name, N'''', N'''''') + N''')' + @crlf
    + N'BEGIN' + @crlf
    + N'    CREATE LOGIN ' + QUOTENAME(p.name) + @crlf
    + N'        WITH PASSWORD = ' + CONVERT(NVARCHAR(MAX), sl.password_hash, 1) + N' HASHED,' + @crlf
    + N'             SID      = ' + CONVERT(NVARCHAR(MAX), p.sid, 1) + N',' + @crlf
    + N'             DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(p.default_database_name, N'master')) + N',' + @crlf
    + N'             DEFAULT_LANGUAGE = ' + QUOTENAME(ISNULL(p.default_language_name, N'us_english')) + N',' + @crlf
    + N'             CHECK_POLICY     = ' + CASE sl.is_policy_checked     WHEN 1 THEN N'ON' ELSE N'OFF' END + N',' + @crlf
    + N'             CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + @crlf
    -- Disabled state is NOT carried by CREATE LOGIN. Re-apply it, or the account
    -- comes back enabled on the target with its original password still valid.
    -- Inside the guard, so it only touches logins this script actually created.
    + CASE WHEN p.is_disabled = 1
           THEN N'    ALTER LOGIN ' + QUOTENAME(p.name) + N' DISABLE;  -- disabled on source' + @crlf
           ELSE N'' END
    + N'END' + @crlf
    + N'GO' + @crlf + @crlf
FROM sys.server_principals p
INNER JOIN sys.sql_logins sl ON p.principal_id = sl.principal_id
WHERE p.type = 'S'
  AND p.name NOT LIKE N'##%##'
  AND p.name NOT IN (N'sa', N'guest', N'public')
ORDER BY p.name;

-- ── Windows logins and groups ─────────────────────────────────────────────────

IF LEN(@ddl) = @len
    SET @ddl = @ddl + N'-- (none found)' + @crlf + @crlf;

SET @ddl = @ddl + N'-- Windows Logins and Groups' + @crlf + N'GO' + @crlf + @crlf;
SET @len = LEN(@ddl);


SELECT @ddl = @ddl
    + N'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(p.name, N'''', N'''''') + N''')' + @crlf
    + N'BEGIN' + @crlf
    + N'    CREATE LOGIN ' + QUOTENAME(p.name) + N' FROM WINDOWS' + @crlf
    + N'        WITH DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(p.default_database_name, N'master')) + N',' + @crlf
    + N'             DEFAULT_LANGUAGE = ' + QUOTENAME(ISNULL(p.default_language_name, N'us_english')) + @crlf
    + CASE WHEN p.is_disabled = 1
           THEN N'    ALTER LOGIN ' + QUOTENAME(p.name) + N' DISABLE;  -- disabled on source' + @crlf
           ELSE N'' END
    + N'END' + @crlf
    + N'GO' + @crlf + @crlf
FROM sys.server_principals p
-- 'U' = WINDOWS_LOGIN, 'G' = WINDOWS_GROUP. There is no type 'W' in
-- sys.server_principals, so a 'W' here matches nothing and silently
-- scripts no Windows logins at all.
WHERE p.type IN ('U', 'G')
  AND p.name NOT LIKE N'##%##'
  AND p.name NOT IN (N'sa', N'guest', N'public')
  AND p.name NOT LIKE N'NT SERVICE\%'
  AND p.name NOT LIKE N'NT AUTHORITY\%'
  AND p.name NOT LIKE N'BUILTIN\%'
ORDER BY p.name;

-- ── Server role memberships ───────────────────────────────────────────────────

IF LEN(@ddl) = @len
    SET @ddl = @ddl + N'-- (none found)' + @crlf + @crlf;

SET @ddl = @ddl + N'-- Server Role Memberships' + @crlf + N'GO' + @crlf + @crlf;
SET @len = LEN(@ddl);


SELECT @ddl = @ddl
    + N'IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(m.name, N'''', N'''''') + N''')' + @crlf
    + N'    ALTER SERVER ROLE ' + QUOTENAME(r.name) + N' ADD MEMBER ' + QUOTENAME(m.name) + N';' + @crlf
    + N'GO' + @crlf + @crlf
FROM sys.server_role_members srm
INNER JOIN sys.server_principals r ON srm.role_principal_id  = r.principal_id
INNER JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id
WHERE r.name <> N'public'
  AND m.name NOT LIKE N'##%##'
  AND m.name NOT IN (N'sa')
  AND m.name NOT LIKE N'NT SERVICE\%'
  AND m.name NOT LIKE N'NT AUTHORITY\%'
ORDER BY r.name, m.name;

IF LEN(@ddl) = @len
    SET @ddl = @ddl + N'-- (none found)' + @crlf + @crlf;

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

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

This script lives in the repo at:

• sql/migration/Generate-LoginScript.sql

---

The Findings: A Filter That Matched Nothing, and a Disabled Account Coming Back Live

Every Windows login and group was silently skipped. The Windows section filtered on WHERE p.type IN ('W', 'G'). There is no type 'W' in sys.server_principals. A Windows login is type 'U' (WINDOWS_LOGIN), a Windows group is 'G'. So the filter matched zero rows on every server it has ever run against, and the generated script contained a -- Windows Logins and Groups heading with nothing underneath it. Nothing failed. The output looked finished.

The second half is what makes it worse: the Server Role Memberships section further down was still emitting ALTER SERVER ROLE ... ADD MEMBER [DOMAIN\user] for those same logins, each one wrapped in an IF EXISTS guard. On the target the login doesn’t exist, so the guard is false, so the statement does nothing. No error there either. In a Windows-authentication shop, the whole login migration could run start to finish and produce no logins at all.

A login disabled on the source came back enabled on the target. CREATE LOGIN always produces an enabled login, and nothing re-applied the state, so is_disabled was quietly dropped in transit. The account arrives on the new server live, with its original password hash and SID intact, meaning the old password still works. On the test instance the login that proved this was a disabled sysadmin, and the script’s own role-membership section then added it straight back to the sysadmin role. The companion Get-MigrationLoginAudit.sql in the same folder has always reported is_disabled and advised you to “migrate or exclude intentionally”, the generator just wasn’t carrying the answer across.

Both are fixed. The type filter is now ('U', 'G'), and an ALTER LOGIN ... DISABLE; is emitted inside the creation guard for any login disabled on the source. DEFAULT_LANGUAGE is now carried too, and identifiers go through QUOTENAME instead of hand-built brackets. Any section that finds nothing now prints -- (none found), so an empty result announces itself instead of looking like a clean run.

---

What Got Verified, and How the First Pass Missed It

The earlier verification on this post said no bug turned up. It was wrong, and the reason is worth more than the bug. That check took the generated CREATE LOGIN ... WITH PASSWORD = ... HASHED, SID = ... for a SQL login, ran it on a throwaway account, confirmed the row in sys.server_principals, and stopped. Every part of that passed. It just never asked whether anything was missing from the output, and the two defects both live in what the script doesn’t emit.

This pass ran the generator against a real instance and read the output against the instance it came from: 7 Windows logins present in sys.server_principals, 0 in the generated script. Then the disabled-login case was proved end to end. A login was created, disabled, scripted with the generator’s own expression, dropped, re-created from that exact generated DDL, and re-checked: is_disabled = 0. The throwaway login was dropped and the drop confirmed.

All 26 DDL generators in the repo were then re-run and every generated script was fed back to SQL Server under SET PARSEONLY ON, including the T-SQL inside each Agent job step. No further syntax defects were found.

---

Best Practices

• Always review the generated DDL before running it on the target. Nothing here executes automatically.
• Run this before restoring the databases, later steps like Generate User Mapping Script need the logins to already exist on the target.
• Preserve the SID, don’t just recreate the login name, an orphaned-user problem from a mismatched SID is much harder to diagnose after the fact than to prevent up front.
• Read the generated script for -- (none found) before you run it. An empty section is a legitimate answer on some servers and a red flag on others, and it is now visible either way.
• Check the disabled logins landed disabled. SELECT name, is_disabled FROM sys.server_principals on both servers, and compare, is a ten-second check that catches a whole class of problem.
• Server-level permissions and explicit DENY CONNECT SQL are deliberately not scripted here. Audit those separately with Get-MigrationLoginAudit.sql.
• After restoring, run Fix-OrphanedUsers.sql to catch anything that didn’t line up cleanly.

---

Related Scripts

You may also find these scripts useful:

• SQL Server Migration Script Generators (hub)
• Generate User Mapping Script
• Generate Restore With Move Script
• Get Migration Risk Assessment
• Migration Login Audit and Post-Migration Validation
• DBA Scripts: The Complete Guide, the map across every script on this site
• Get Login Migration Parity, compare login attributes between the source and target after the transfer
• SQL Server Login Migration: What Gets Silently Left Behind, the attributes a login transfer does not carry

---

Frequently Asked Questions

---

Summary

One generator, one job: recreate every login on the target with its original SID and password hash intact, so the databases restored afterward don’t end up with orphaned users. As of 2026-08-18 that genuinely includes Windows logins and groups, which a wrong type code had been silently excluding, and a login disabled on the source now arrives disabled instead of live. Run this first in a migration, before the databases are even restored, and read the output for -- (none found) before you trust it.