LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon