How to Migrate SQL Server Logins Between Servers

When migrating SQL Server databases between servers, you can't just restore the database and call it done. You also need to migrate the SQL Server logins, and if you want users to keep their passwords and permissions, you need to do it correctly.

This guide shows you how to use the sp_help_revlogin stored procedure to migrate SQL Server logins while preserving passwords, SIDs, server roles, and other login properties. This is the proper way to move logins between servers without breaking authentication or requiring password resets.

Why You Can't Just Create Logins Manually

SQL Server uses Security Identifiers (SIDs) to link database users to server logins. When you create a login with CREATE LOGIN and set a password, SQL Server generates a new SID. Even if the username and password match, the SID won't match the one in your database, which means orphaned users.

The sp_help_revlogin stored procedure solves this by:

• Extracting the password hash from the source server
• Preserving the original SID
• Generating CREATE LOGIN statements with the exact password hash and SID
• Including server role memberships
• Preserving login properties like default database and language

Prerequisites

Before you start, make sure you have:

• Administrative access to both source and destination SQL Server instances
• SQL Server Management Studio (SSMS) or similar tool
• Permission to create stored procedures in the master database
• A backup plan (always have backups before migration)

Step 1: Create the Stored Procedures on Source Server

First, connect to your source SQL Server instance and run the following script in the master database. This creates two stored procedures:

• sp_hexadecimal: Helper procedure that converts binary values to hexadecimal
• sp_help_revlogin: Main procedure that generates the login migration script

USE [master]
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE [dbo].[sp_hexadecimal]
(
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
)
AS
BEGIN
    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
END
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin]   
(
    @login_name sysname = NULL 
)
AS
BEGIN
    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 @Prefix                   VARCHAR(255)
    DECLARE @defaultdb                SYSNAME
    DECLARE @defaultlanguage          SYSNAME     
    DECLARE @tmpstrRole               VARCHAR (1024)

    IF (@login_name IS NULL)
    BEGIN
        DECLARE login_curs CURSOR 
        FOR 
            SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
            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'
            AND p.name not like '##%'
            ORDER BY p.name
    END
    ELSE
            DECLARE login_curs CURSOR 
            FOR 
                SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
                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
                ORDER BY p.name

            OPEN login_curs 
            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
            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

                    SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                    BEGIN'
                    Print @tmpstr 

                    IF (@type IN ( 'G', 'U'))
                    BEGIN
                      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
                    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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

                            IF ( @is_policy_checked IS NOT NULL )
                            BEGIN
                              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
                            END

                            IF ( @is_expiration_checked IS NOT NULL )
                            BEGIN
                              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
                            END
            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 

            SET @Prefix = '
            EXEC master.dbo.sp_addsrvrolemember @loginame='''

            SET @tmpstrRole=''

            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
                + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
                + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
                + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
                + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
                + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
                + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
                + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
              FROM (
                        SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
                                sysadmin,
                                securityadmin,
                                serveradmin,
                                setupadmin,
                                processadmin,
                                diskadmin,
                                dbcreator,
                                bulkadmin
                        FROM sys.syslogins
                        WHERE (       sysadmin<>0
                                OR    securityadmin<>0
                                OR    serveradmin<>0
                                OR    setupadmin <>0
                                OR    processadmin <>0
                                OR    diskadmin<>0
                                OR    dbcreator<>0
                                OR    bulkadmin<>0
                            ) 
                            AND name=@name 
                  ) L 

                PRINT @tmpstr
                PRINT @tmpstrRole
                PRINT 'END'
            END 
            FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
        END
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN 0
END
GO

Execute this script in the master database on your source server. It will create both stored procedures.

Step 2: Generate the Login Migration Script

Now you need to execute sp_help_revlogin to generate the migration script. You have two options:

Option A: Generate Scripts for All Logins

To generate scripts for all SQL Server logins (excluding 'sa' and logins starting with '##'):

EXEC sp_help_revlogin

Option B: Generate Script for a Specific Login

To generate a script for a specific login:

EXEC sp_help_revlogin 'YourLoginName'

Step 3: Get the Output in Text Format

The output from sp_help_revlogin is sent to the Messages tab in SSMS. To capture all of it:

1. In SSMS, go to Query → Results To → Results to Text (or press Ctrl+T)
2. Execute sp_help_revlogin again
3. Copy all the output from the Results pane

Important: Make sure you're in "Results to Text" mode before executing. The output includes CREATE LOGIN statements with password hashes and SIDs that you'll need to run on the destination server.

Step 4: Execute on Destination Server

Now connect to your destination SQL Server instance:

1. Open a new query window connected to the destination server
2. Paste the entire output from Step 3
3. Review the script (check for any logins that shouldn't be migrated)
4. Execute the script

The script uses IF NOT EXISTS checks, so it's safe to run even if some logins already exist. However, if a login already exists with a different SID, you'll get an error.

Step 5: Fix Orphaned Database Users

After migrating the logins and restoring your databases, you may still have orphaned users. To fix them:

-- For each database, run:
USE [YourDatabase]
GO

-- This fixes orphaned users by linking them to the login with the same name
EXEC sp_change_users_login 'UPDATE_ONE', 'DatabaseUser', 'ServerLogin'
GO

-- Or to auto-fix all orphaned users in the database:
EXEC sp_change_users_login 'AUTO_FIX', 'DatabaseUser'
GO

What Gets Migrated

The sp_help_revlogin procedure captures and migrates:

• Login name
• Password hash (for SQL authentication logins)
• SID (Security Identifier)
• Default database
• Default language
• Password policy settings (CHECK_POLICY, CHECK_EXPIRATION)
• Login state (enabled/disabled)
• Access permissions (hasaccess, denylogin)
• Server role memberships (sysadmin, securityadmin, etc.)

Important Notes and Warnings

Windows Authentication Logins

Windows authentication logins (type 'U' or 'G') don't have passwords stored in SQL Server. The migration script will create the login, but the actual authentication is handled by Windows/Active Directory. Make sure the Windows accounts exist on the destination server or in the same domain.

Server-Level Permissions

This script migrates server roles but not granular server-level permissions. If you've granted specific permissions using GRANT statements, you'll need to migrate those separately.

Linked Server Logins

Linked server mappings are not migrated by this script. You'll need to recreate those manually or use a separate script.

Database-Level Permissions

This script only migrates server-level logins. Database users and their permissions are part of the database and come over when you restore the database. You'll still need to fix orphaned users as described in Step 5.

Testing

Always test the migration on a non-production server first. Verify that:

• Logins can authenticate successfully
• Database users are properly linked
• Permissions work as expected
• Applications can connect using the migrated logins

Troubleshooting

Error: "Login already exists"

If a login already exists on the destination server, you'll need to either drop it first (if safe to do so) or modify the generated script to skip that login.

Error: "Invalid password hash"

This can happen if SQL Server versions are significantly different. Make sure you're migrating between compatible SQL Server versions.

Orphaned Users After Migration

If database users show as orphaned after migration, use sp_change_users_login as described in Step 5. Make sure the SIDs match between the login and database user.

Alternative: Using Scripts for Specific Logins

If you only need to migrate a few logins, you can run sp_help_revlogin with a login name parameter for each one:

EXEC sp_help_revlogin 'Login1'
EXEC sp_help_revlogin 'Login2'
EXEC sp_help_revlogin 'Login3'

This gives you more control over which logins are migrated and makes it easier to review the output.

Conclusion

Migrating SQL Server logins properly requires preserving passwords and SIDs. The sp_help_revlogin stored procedure is the standard Microsoft-recommended approach for this task. By following these steps, you can migrate logins between servers without breaking authentication or requiring users to reset their passwords.

Remember to always test migrations in a non-production environment first, have backups ready, and verify that applications can connect successfully after the migration.