An orphaned SQL login occurs when a SQL Server login exists at the server level, but its corresponding database user is no longer properly mapped within a specific database. This often happens after restoring a database from another server or detaching and reattaching a database.

Why It Happens

Each SQL login has a SID (Security Identifier). When a database is moved or restored, the login’s SID might not match the SID of the user in the database, causing the user to become “orphaned.”

How to Identify Orphaned Users

You can run this query inside the database:

SELECT dp.name AS user_name, dp.type_desc, dp.sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc IN ('INSTANCE', 'WINDOWS')
  AND sp.sid IS NULL
  AND dp.principal_id > 4;

This lists database users whose SID has no matching login on this server. Users created WITHOUT LOGIN are excluded, as they’re meant to have no login.

How to Fix Orphaned Users

\1. Re-map the user to an existing login:

EXEC sp_change_users_login 'Update_One', 'username', 'loginname';

Optionally, you can add a password if the login doesn’t exist:

EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, '<your password here>';

\2. Use ALTER USER (modern method):

ALTER USER [username] WITH LOGIN = [loginname];

This is the preferred method in newer versions of SQL Server (2012+).

Tip

To avoid orphaned users when restoring databases, make sure logins are scripted and recreated on the target server with the same SID using:

the script in Replicating SQL Server Logins with Their SIDs, or Microsoft’s sp_help_revlogin procedure.

Scripts

-- Step 1: List orphaned users
EXEC sp_change_users_login 'Report';

-- Step 2: Automatically fix orphaned users by mapping them to logins with the same name
DECLARE @username NVARCHAR(100);

DECLARE orphaned_users CURSOR FOR
SELECT name FROM sys.database_principals
WHERE type = 'S' AND authentication_type_desc = 'INSTANCE' AND sid NOT IN (SELECT sid FROM sys.server_principals);

OPEN orphaned_users;
FETCH NEXT FROM orphaned_users INTO @username;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Fixing orphaned user: ' + @username;
    EXEC sp_change_users_login 'Auto_Fix', @username;
    FETCH NEXT FROM orphaned_users INTO @username;
END

CLOSE orphaned_users;
DEALLOCATE orphaned_users;

Notes

• This script uses sp_change_users_login, which is deprecated in newer versions of SQL Server. For modern systems, consider using ALTER USER:

ALTER USER [username] WITH LOGIN = [loginname];

Make sure the login exists on the server before mapping.

Always test in a development environment before running on production.

From my SQL Server DBA handbook.