Find and fix orphaned SQL Server database users
Queries to find orphaned database users, users mapped to an expired certificate, and commands to map them to a login.
An orphaned user is a database user whose SID does not match any server login, for example after a database has been restored on another server. The approach below follows the article Different ways to find SQL Server orphaned users.
Find orphaned users
Run the query in the database concerned. It excludes the system users and the users without login that have a 28-byte SID (these are valid).
-- find orphaned users from windows/certificate/asymmetric_key login
select dp.name, dp.type, dp.sid, LEN(dp.sid) as [SID_Len]
from sys.database_principals dp
left join sys.server_principals sp
on dp.sid = sp.sid
left join sys.certificates c
on dp.sid = c.sid
left join sys.asymmetric_keys a
on dp.sid = a.sid
where sp.sid is null and c.sid is null and a.sid is null
-- check dp.type, go to the following
--https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql
and dp.type in ('U', 'S', 'C', 'K')
and dp.principal_id > 4 -- 0..4 are system users which will be ignored
and not (dp.type = 'S' and LEN(dp.sid) = 28) -- to filter out the valid db users without login
Users mapped to an expired certificate
select [DBUser]= dp.name
, [certificate]=mc.name
, mc.expiry_date
, curr_date = GETDATE()
from sys.database_principals dp
inner join master.sys.certificates mc
on dp.sid = mc.sid
where mc.expiry_date < getdate();
Map a user to a login
List the unmapped users, then map those whose name matches an existing login:
EXEC sp_change_users_login 'Report';
EXEC sp_change_users_login 'auto_fix', 'app_user';
Check a Windows login
exec xp_logininfo 'DOMAIN\user';
Notes
sp_change_users_loginis deprecated. To map a user to a login, the recommended syntax isALTER USER ... WITH LOGIN.auto_fixmaps the user to the login with the same name. Check the output ofReportbefore running it.