Orphaned Users of SQLServer

Created2023-05-20|Updated2026-07-30|DatabaseSQLServer

|Word Count:97|Reading Time:1mins|Post Views:

Orphaned Users of SQLServer

Detect orphaned users.

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';

Resolve an orphaned user.

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

ALTER USER <user_name> WITH Login = <login_name>;

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';