Part of the DBA-Tools Project.
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.
Why SID-Preserving Login Recreation Matters
- A
CREATE USERmapped 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 LOGINstatement 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.
/*
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)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @ddl NVARCHAR(MAX) = N'';
DECLARE @crlf NCHAR(2) = CHAR(13) + CHAR(10);
-- ... full DDL-assembly section in the repo, link below ...
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-LoginScript.ps1
# Output: output-files\migration\*.sql
This script lives in the repo at:
Verified Against a Real Instance
Ran for real: 149 lines, 9 real logins with hashed passwords, SIDs, and role memberships. Took the exact generated CREATE LOGIN ... WITH PASSWORD = ... HASHED, SID = ... syntax, created a real throwaway login from it, confirmed it in sys.server_principals, then dropped it. No bug turned up in this generator, the syntax was correct as read, and the round-trip confirmed it against a real instance rather than trusting the read-through alone.
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.
- After restoring, run
Fix-OrphanedUsers.sql(a planned addition to this set) 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
Frequently Asked Questions
Why does preserving the SID matter so much?
A database user is mapped to a server login by SID, not by name. If the target’s login has a different SID than the source database expected, CREATE USER ... FOR LOGIN either fails to map correctly or creates an orphaned user, one that exists in the database but can’t authenticate through the login it’s supposed to be tied to.
Does this script move the actual login passwords in plain text?
No. It extracts the password hash directly from sys.sql_logins and recreates the login with CREATE LOGIN ... WITH PASSWORD = <hash> HASHED, the plain-text password itself is never read or exposed.
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. Verified with a real throwaway login, created from the generated DDL and confirmed in sys.server_principals. Run this first in a migration, before the databases are even restored.
Leave a Reply