Home / Guides / Transfer SQL Server logins with their SID and password

Transfer SQL Server logins with their SID and password

A backup or restore moves the database users to the new instance, but not the logins they belong to. If you create the logins again by hand, the users are orphaned and people get "Login failed". This guide copies the logins with their original SID and password hash, fixes the users that are already orphaned, and checks that both servers really match.

Why the SID and the password hash matter

A database user points at its login by SID, not by name. A SQL Server login gets a new SID every time it is created, so CREATE LOGIN app_user WITH PASSWORD = '...' on the new server gives you the right name with the wrong SID. The user in the restored database no longer matches any login: it is orphaned, and sign-ins fail with error 18456. Copying the login with its original SID avoids that, and copying the password hash (instead of a new password) means nobody has to change a connection string.

Windows logins are different: their SID comes from Windows, so a domain account has the same SID on every server in the domain. The availability group guide covers the SID rules in more detail.

Method 1: Microsoft's sp_help_revlogin

Microsoft publishes this method in the article Transfer logins and passwords between instances of SQL Server (the old KB 918992 and 246133). The article holds the full script; copy it from there, because Microsoft keeps it up to date. The steps:

1. On the source instance, connect as a member of sysadmin and run Microsoft's script. It creates two procedures in master: sp_hexadecimal and sp_help_revlogin.
2. In SSMS, switch to Results to Text (the procedure prints the script as messages).
3. Run the procedure for every login, or for one login:

USE [master];
-- every login (sa and ## system logins are left out by the procedure)
EXEC dbo.sp_help_revlogin;

-- or one login
EXEC dbo.sp_help_revlogin @login_name = N'app_user';

4. Review the output (see the next section), then run it on the destination instance.

Each login comes out as one block like this (the password hash is shortened here):

/* Login [app_user] */
IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N'app_user'
    )
BEGIN
    CREATE LOGIN [app_user]
    WITH PASSWORD = 0x030040C9...(hash shortened)... HASHED
        ,SID = 0x7E45CCBAF96C9042BC40B91FC3DA9054
        ,DEFAULT_DATABASE = [master]
        ,DEFAULT_LANGUAGE = [us_english]
        ,CHECK_POLICY = OFF
        ,CHECK_EXPIRATION = OFF
END

Treat the output like a password file: it holds every SQL login's password hash. Do not leave it in a ticket, a share or a repository.

Read the output before you run it

• It includes logins you may not want. In our test it also scripted the instance's own administrator login and an NT SERVICE account. Delete the blocks you do not want to create on the destination.
• An existing login is skipped without a message. Every block starts with IF NOT EXISTS. If the destination already has a login with that name but a different SID (someone created it by hand), the script leaves it alone and the database user stays orphaned. Our verify query below finds these.
• The default database must exist. The current script writes DEFAULT_DATABASE into each statement (Microsoft's article says the default database is not transferred, but the script published now does write it). If that database is not on the destination yet, that one CREATE LOGIN fails with Msg 15010 and the login is not created. Restore the databases first, or change it to [master] and set it later with ALTER LOGIN ... WITH DEFAULT_DATABASE. The script is safe to run again: logins that already exist are skipped.
• Name or SID clashes. Msg 15025 means the name is already there; Msg 15433 ("Supplied parameter sid is in use") means another login on the destination already has that SID. Microsoft's article says to resolve these by hand.
• Different domains or collations. Windows logins from another domain get a different SID, so their users are orphaned. A case-sensitive destination can reject passwords that worked on a case-insensitive source. Both are covered in Microsoft's article.

What the script copies, and what it does not

We ran the script against a test instance and compared the result with the source. This is what arrived:

Version caveats: which password hash you have

CREATE LOGIN ... HASHED stores the hash exactly as it was. Which servers can then check a password against it depends on the hash format, and the first bytes of the hash tell you which one you have:

USE [master];
SELECT name, LEFT(CONVERT(varchar(10), password_hash, 1), 6) AS hash_version
FROM sys.sql_logins
WHERE name NOT LIKE '##%'
ORDER BY name;

Our test instances were SQL Server 2025 LocalDB, so every hash started with 0x0300. We did not test a copy to an older version.

Method 2: dbatools Copy-DbaLogin

If you work in PowerShell, the free dbatools module copies logins directly from one instance to another, with the original SID and password. According to its documentation it also copies server roles, permissions and password policy settings, skips a login that already exists unless you add -Force (which drops and recreates it), and copies logins from SQL Server 2012 or later only to SQL Server 2012 or later. Typical calls, from the dbatools documentation:

# every login, leaving out NT SERVICE and other system logins
Copy-DbaLogin -Source SQL01 -Destination SQL02 -ExcludeSystemLogins

# only these logins
Copy-DbaLogin -Source SQL01 -Destination SQL02 -Login app_user, report_reader

We did not run dbatools for this guide; the commands and the behavior above come from its documentation (linked below). Run the verify query afterwards either way.

Fix orphaned users after the restore

Run this in each restored database on the destination. It is the query from Microsoft's orphaned-users article:

-- USE [YourDatabase];
SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.authentication_type_desc = 'INSTANCE';

There are two ways to fix a user it lists:

Quick fix: point the user at the login that is there

USE [YourDatabase];
ALTER USER [legacy_user] WITH LOGIN = [legacy_user];

ALTER USER ... WITH LOGIN replaces the user's SID in the database with the SID of the login on this server. It replaces the deprecated sp_change_users_login. The catch: the login keeps its own SID and password, so the next restore from the source orphans the user again.

Lasting fix: give the login the source SID

Drop the login on the destination, run sp_help_revlogin for that one login on the source, run its output on the destination, and run the ALTER USER above once more. After that, the login has the same SID and password as on the source, and later restores need no fix. DROP LOGIN refuses a login that is connected or that owns a server-level object or an Agent job, so check that first.

-- 1. On the DESTINATION
USE [master];
DROP LOGIN [legacy_user];

-- 2. On the SOURCE: script that one login, then run its output on the destination
USE [master];
EXEC dbo.sp_help_revlogin @login_name = N'legacy_user';

-- 3. On the DESTINATION, in each database that has the user
USE [YourDatabase];
ALTER USER [legacy_user] WITH LOGIN = [legacy_user];

Verify after the copy: compare SIDs and hashes

Run this on both servers. It lists each login with its SID, a fingerprint of the password hash (a SHA-256 of the hash, so the hash itself is never shown), and the settings, server roles and server permissions that most often differ:

USE [master];
SELECT p.name,
       p.type_desc,
       CONVERT(varchar(200), p.sid, 1) AS sid,
       CONVERT(varchar(66), HASHBYTES('SHA2_256', l.password_hash), 1) AS password_fingerprint,
       p.is_disabled,
       p.default_database_name,
       l.is_policy_checked,
       l.is_expiration_checked,
       ISNULL(STUFF((SELECT ', ' + r.name
                     FROM sys.server_role_members AS rm
                     JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
                     WHERE rm.member_principal_id = p.principal_id
                     ORDER BY r.name
                     FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, ''), '') AS server_roles,
       ISNULL(STUFF((SELECT ', ' + sp.state_desc + ' ' + sp.permission_name
                     FROM sys.server_permissions AS sp
                     WHERE sp.grantee_principal_id = p.principal_id
                       AND NOT (sp.permission_name = 'CONNECT SQL' AND sp.state = 'G')
                     ORDER BY sp.permission_name
                     FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, ''), '') AS server_permissions
FROM sys.server_principals AS p
LEFT JOIN sys.sql_logins AS l ON l.principal_id = p.principal_id
WHERE p.type IN ('S', 'U', 'G')
  AND p.name NOT LIKE '##%'
  AND p.name NOT LIKE 'NT SERVICE\%'
  AND p.name NOT LIKE 'NT AUTHORITY\%'
  AND p.name <> 'sa'
ORDER BY p.name;

Two logins with the same fingerprint have identical hashes, so the same password. To compare two servers in one step, save the query as login-fingerprint.sql and run this PowerShell script (Windows authentication; it only reads):

param(
    [Parameter(Mandatory)] [string]$Source,
    [Parameter(Mandatory)] [string]$Target,
    [string]$QueryFile = '.\login-fingerprint.sql'
)
$ErrorActionPreference = 'Stop'
$query = Get-Content -Raw $QueryFile

function Get-LoginRows([string]$server) {
    $cn = New-Object System.Data.SqlClient.SqlConnection "Server=$server;Database=master;Integrated Security=True"
    $cn.Open()
    try {
        $da = New-Object System.Data.SqlClient.SqlDataAdapter ($query, $cn)
        $t = New-Object System.Data.DataTable
        [void]$da.Fill($t)
        $t.Rows | ForEach-Object {
            [pscustomobject]@{ Login = $_.name; Line = ($_.ItemArray -join ' | ') }
        }
    } finally { $cn.Close() }
}

$a = Get-LoginRows $Source
$b = Get-LoginRows $Target
Compare-Object $a $b -Property Line -PassThru |
    Sort-Object Login, SideIndicator |
    Select-Object @{n='Server'; e={ if ($_.SideIndicator -eq '<=') { $Source } else { $Target } }}, Line |
    Format-Table -AutoSize -Wrap

No output means the logins match. In our test, before the fixes, it showed exactly the gaps described above: a login created by hand with a different SID and fingerprint, and a login whose user-defined server role and VIEW SERVER STATE permission had not been copied (server names replaced and fingerprints shortened here).

Server  Line
------  ----
SQL01   legacy_user | SQL_LOGIN | 0x7C65F636E8FE7E4FAFF8D5AFB848AE53 | 0x22F5A33A... | False | master | False | False |  |
SQL02   legacy_user | SQL_LOGIN | 0x4A9915B64ED2B94ABF53AF28F1A5699C | 0xAFA24562... | False | master | False | False |  |
SQL01   ops_admin | SQL_LOGIN | 0x342196AD6ACBA44B8D51001D737E7EAF | 0xBA029790... | False | master | False | False | dbcreator, guide_monitor | GRANT VIEW SERVER STATE
SQL02   ops_admin | SQL_LOGIN | 0x342196AD6ACBA44B8D51001D737E7EAF | 0xBA029790... | False | master | False | False | dbcreator |

Keeping them the same afterwards

A transfer is a one-off. After it, a password changed on one server, a new login, or a role granted on one side puts the servers out of step again, and you find out at the next restore or failover. You can rerun the verify script on a schedule, rerun Copy-DbaLogin, or use a tool that keeps watching.

Questions

Related

• Sync SQL Server logins and Agent jobs across availability group replicas
• SQLParity: keep your SQL Server instances the same
• Remote SQL Server DBA services
• All guides

Sources (Microsoft Learn): Transfer logins and passwords between instances of SQL Server, Troubleshoot orphaned users, ALTER USER, CREATE LOGIN, DROP LOGIN, LOGINPROPERTY (hash versions), Trace flag 4671. dbatools: Copy-DbaLogin.