Beyond DMS: Accelerating Migrations SQL Server Logins and Users to Cloud SQL
Google Cloud explains why SQL Server logins do not migrate automatically during database moves and provides a Microsoft tool to safely recreate them in Cloud SQL for SQL Server.
Database Migration Service efficiently copies database schemas and data but intentionally skips instance-level objects like server logins and permissions. This design prevents privilege escalation and ensures compliance with frameworks such as PCI-DSS or SOC 2 by keeping security object migration under administrator control. The gap between source and destination environments protects against unauthorized access and maintains clear governance boundaries during cloud adoption.
SQL Server separates identities into server-level logins and database-level users, linked by unique Security Identifiers. When a database is migrated without its corresponding logins, users become "orphaned," retaining permissions but unable to authenticate. This mismatch breaks the connection between access rights and identity, requiring manual resolution to restore functionality.
To resolve orphaned users, administrators can use Microsoft’s sp_help_revlogin script, which generates CREATE LOGIN statements with original password hashes and Security Identifiers. The script must be executed on the source instance first, then the output is run against the destination Cloud SQL for SQL Server instance to recreate logins with intact credentials and SIDs.
The process ensures that database-level users automatically map to their server-level logins after migration, eliminating orphaned users. Google Cloud recommends using the latest version of sp_help_revlogin and reviewing Microsoft’s documentation to maintain security and compatibility throughout the migration.