How to find and remove Orphan database users on SQL Server 2019

Orphan users come along when you restore the database from one server to different.  provide a small security risk if some newly defined login is unintentionally associated with an orphan user, allowing the new login to gain unauthorized database access.

Orphan users are those which exist in the database but have no entry in master DB.

So here is how you identify and remove the orphaned users.

In query window,  Select the database for which you want to find the Orphan users and execute the following query

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 authentication_type_desc = 'INSTANCE';  

It will list all the users associated with the selected database.

Now you either create a login in the master database.

1. Create and MAP
2. Remove Orphan user

1. Create and MAP – Use this if you still want to use the old username to connect to DB. Easy method is to create a new user after restore and remove orphan which come along during database restore.

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

To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

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

When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.

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

Source : https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15

2. Removing Orphan Users

exec sp_revokedbaccess 'USERX'

USERX is the orphan username

We used the 2nd option to resolve orphan username issues at our end.