Transfer / Migrate users between versions of SQL Server

Why Migrating SQL Server Logins Is Harder Than It Looks

Moving a database from one SQL Server instance to another is rarely as simple as detaching and reattaching files. The databases themselves transfer cleanly enough, but the logins are a different story. SQL Server logins are stored in the master database with password hashes and security identifiers (SIDs) that don't automatically follow your databases to the new instance. Get this wrong and your application users will hit authentication errors the moment they try to connect, even though the database itself looks perfectly healthy.

The correct way to migrate SQL Server logins between instances is to use Microsoft's sp_help_revlogin stored procedure, which scripts out existing logins with their original password hashes and SIDs intact. This preserves the link between logins and database users, avoiding the orphaned user problem that catches so many administrators off guard.

What Is the Orphaned User Problem?

Every SQL Server login has a SID, a unique binary identifier assigned when the login is created. Inside each database, users are mapped to logins using that SID. When you restore a database to a new instance and then manually recreate the logins, SQL Server assigns new SIDs to those logins. The result is a mismatch: the database user points to a SID that no longer exists in the master database. The user is "orphaned."

Orphaned users cause login failures even when the username and password are correct. You can identify them with EXEC sp_change_users_login 'Report' inside the affected database, but it's far better to avoid the problem entirely by migrating logins properly in the first place.

How to Migrate SQL Server Logins Using sp_help_revlogin

Microsoft's Knowledge Base article KB918992 provides the sp_help_revlogin procedure for SQL Server 2005 and later. The process has two stages: create the procedure on the source instance, run it to generate a login script, then execute that script on the destination instance.

Step 1: Create the Helper Stored Procedure on the Source Instance

Connect to your source SQL Server and run the following in the master database. This creates the sp_hexadecimal helper procedure and the main sp_help_revlogin procedure.

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS

DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)

SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,
           l.hasaccess, l.denylogin
    FROM sys.server_principals p
    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
    WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,
           l.hasaccess, l.denylogin
    FROM sys.server_principals p
    LEFT JOIN sys.syslogins l ON ( l.name = p.name )
    WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END

SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''

WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN
      SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
      EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
      EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

      SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE 'NULL' END
      FROM sys.sql_logins WHERE name = @name

      SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE 'NULL' END
      FROM sys.sql_logins WHERE name = @name

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + '], CHECK_POLICY = ' + @is_policy_checked + ', CHECK_EXPIRATION = ' + @is_expiration_checked
    END

    IF (@denylogin = 1)
    BEGIN
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END

    IF (@is_disabled = 1)
    BEGIN
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END

    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs
  INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END

CLOSE login_curs
DEALLOCATE login_curs
GO

Step 2: Generate the Login Script

Once the procedure exists, execute it to produce the migration script. You can script all logins at once, or target a single login by name.

-- Script all logins
EXEC sp_help_revlogin

-- Script a single login
EXEC sp_help_revlogin 'YourLoginName'

The output will be a series of CREATE LOGIN statements with hashed passwords and explicit SIDs. Copy this output and run it on your destination SQL Server instance.

Step 3: Execute the Script on the Destination Instance

Connect to the destination instance and run the generated script against the master database. Because the SIDs are preserved, any existing database users will automatically re-link to the correct logins without needing any orphan remediation.

What About Older Versions of SQL Server?

If you're migrating from SQL Server 2000 or earlier, the sp_help_revlogin procedure won't work because the system table structure is different. Microsoft's older Knowledge Base article KB246133 covers the equivalent process for SQL Server 7.0 and 2000. The principle is identical, but the script queries the legacy sysxlogins table rather than sys.server_principals.

In practice, if you're still running SQL Server 2000 in 2024, migrating logins is the least of your concerns. That version has been out of support since 2008 and carries serious security risk. A full upgrade path assessment is well overdue.

What to Watch Out For

Migrating logins with this method is reliable, but there are a few things worth checking before and after the migration.

• Windows logins: The procedure scripts these correctly, but they'll only work on the destination server if the Windows accounts or groups exist in Active Directory. Domain-joined servers in the same environment won't have any issues. Cross-domain migrations need additional planning.
• The sa account: The procedure deliberately excludes the sa login. You'll need to handle that separately if required.
• Server-level permissions: sp_help_revlogin captures the login itself, but not all server-level role memberships or explicit server permissions. Review sys.server_role_members and sys.server_permissions on the source instance to make sure nothing is missed.
• Linked server logins: These are not captured by this procedure and need to be scripted separately.
• Default database availability: If a login's default database doesn't exist on the destination instance, the login will be created but the user may receive errors on connection. Verify all referenced databases exist before running the script.

Key Takeaways

• SQL Server logins don't migrate automatically with databases. You must explicitly script and transfer them to avoid orphaned users and authentication failures.
• Microsoft's sp_help_revlogin procedure (KB918992) is the correct tool for migrating logins between SQL Server 2005 and later instances. It preserves password hashes and SIDs.
• Preserving SIDs is critical. It's what prevents the orphaned user problem that breaks application connectivity after a migration.
• The procedure excludes sa, Windows login validation, server-level permissions, and linked server logins. Check all four manually after migration.
• SQL Server 2000 and earlier require a different script from KB246133, but any environment still running those versions needs urgent attention beyond just a login migration.

Migrating logins is one of those tasks that looks straightforward until something goes wrong at 2am after a cutover. If you're planning a SQL Server migration and want confidence that logins, permissions, and security configurations will transfer cleanly, DBA Services can help. Our SQL Server health checks include a full review of login configurations, orphaned users, and security posture across your instances, and our managed support team has handled hundreds of SQL Server migrations across Australian organisations of all sizes. Get in touch to talk through your migration requirements.