SQL Server orphaned users after a restore or migration - finding and re-linking them to logins

#Symptoms

After restoring or attaching a database on a new server:

• The app gets Login failed for user 'app_user' (error 18456) even though the login exists.
• Or the login connects but gets The server principal "app_user" is not able to access the database "Sales" under the current security context (error 916).
• CREATE LOGIN succeeded, but the user in the database still doesn't work.

#Why it happens

A login lives in master and has a SID. A database user stores the SID of the login it belongs to. SQL logins created separately on two servers get different SIDs, so after a restore the user's SID matches no login on the new server. The user is orphaned.

Windows logins (domain accounts and groups) use the domain SID, so they usually re-link on their own when the login exists on the new server.

#1. Find orphaned users

Run in the restored database:

USE Sales;
SELECT dp.name AS user_name, dp.type_desc, dp.sid
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'
  AND dp.type IN ('S','U','G')
  AND dp.principal_id > 4;   -- skip dbo, guest, sys, INFORMATION_SCHEMA

authentication_type_desc = 'INSTANCE' limits the list to users that should map to a login. Contained database users (DATABASE) and users without login (NONE) are expected to have no matching login.

Across all databases on the instance:

EXEC sp_MSforeachdb N'
USE [?];
SELECT DB_NAME() AS db, dp.name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL AND dp.authentication_type_desc = ''INSTANCE''
  AND dp.type IN (''S'',''U'',''G'') AND dp.principal_id > 4;';

sp_MSforeachdb is undocumented. It's fine for an ad-hoc check, but use a loop over sys.databases in production scripts.

#2. Re-link a user to an existing login

USE Sales;
ALTER USER [app_user] WITH LOGIN = [app_user];

This updates the user's SID to match the login. The user keeps its role memberships and permissions.

If the login doesn't exist yet, create it first:

CREATE LOGIN [app_user] WITH PASSWORD = N'<strong password>', CHECK_POLICY = ON;
ALTER USER [app_user] WITH LOGIN = [app_user];

sp_change_users_login does the same thing but is deprecated. Use ALTER USER ... WITH LOGIN.

Fix every orphaned SQL user that has a login of the same name:

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'ALTER USER ' + QUOTENAME(dp.name) + N' WITH LOGIN = ' + QUOTENAME(dp.name) + N';' + CHAR(10)
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL AND dp.type = 'S' AND dp.authentication_type_desc = 'INSTANCE'
  AND dp.principal_id > 4
  AND EXISTS (SELECT 1 FROM sys.server_principals s WHERE s.name = dp.name);
PRINT @sql;   -- review first
-- EXEC sys.sp_executesql @sql;

#3. Better: copy logins with the same SID

Create SQL logins on the target with the same SID and password hash as the source, so users never become orphaned, even on future restores. Generate the statements on the source:

SELECT 'CREATE LOGIN ' + QUOTENAME(name)
     + ' WITH PASSWORD = ' + CONVERT(varchar(max), CAST(LOGINPROPERTY(name, 'PasswordHash') AS varbinary(256)), 1) + ' HASHED'
     + ', SID = ' + CONVERT(varchar(max), sid, 1)
     + ', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name)
     + ', CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
     + ', CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ';'
FROM sys.sql_logins
WHERE name NOT LIKE '##%' AND name <> 'sa';

Microsoft's sp_help_revlogin script does the same and also handles Windows logins. The dbatools PowerShell module can copy logins between instances:

Copy-DbaLogin -Source sqlold01 -Destination sqlnew01 -Login app_user, report_user

Warning

The generated script contains password hashes. Treat it like a password file: don't commit it to Git or email it, and delete it after use.

#4. Availability groups and log shipping

Secondary replicas need the same logins with the same SIDs. Otherwise users are orphaned the moment you fail over. Copy logins to every replica as part of the AG build, and again whenever a login is added.

For simpler cases, consider contained database users (CREATE USER x WITH PASSWORD = ... in a contained database) or Entra ID authentication on Azure SQL. Neither depends on server logins.

#Checklist

• Orphaned users listed in each restored database
• Re-linked with ALTER USER ... WITH LOGIN
• Logins copied with matching SIDs for future restores
• All AG/log shipping replicas have the same logins
• Scripts with password hashes deleted