Orphan users refer to those present at the database level without corresponding logins at the server level. They typically arise during actions like database backup and restoration, often occurring during database migrations. In essence, when the mapping between SQL Server logins and database users is improperly defined for SQL Server principals, the login will fail for that particular database user on the specific instance, leading to the creation of an orphan user. To locate orphaned users in SQL Server, utilize the following command:

USE  <DATABASE_NAME>

EXEC SP_CHANGE_USERS_LOGIN ‘REPORT’

GO

Ways to Fix Orphan Users:

Type1:

If login is available at SQL Level and User is available at Database level and not mapped,can use autofix method.

Type2:

If User is available and login not available

Create Login with same name as User and use above Auto Fix Option

If login not available, use below command to create.

CREATE LOGIN [LoginName] WITH PASSWORD = ‘akeesoft@123’

If Orphan user is fixed the output of query will be blank