Find the Orphan users in a database of SQL Server
What is an Orphan user:
A user which didn't have its assosiate Login in the database engine is termed as "Orphan User".
These kind of situation generally arises when migration of databases occur.
How to trace the Orphan User:
/*RUN in DB.Gives users not having logins in security at server level */
select * from sys.sysusers where sid NOT in (select distinct sid from sys.syslogins)
and gid =0 and name not in ('public','guest')
If Orphan users are found then remap the users to the Logins.
Above query will give all the details of users.
select name,SUSER_SNAME(sid) from sys.sysusers where sid NOT in (select distinct sid from sys.syslogins) and gid =0 and name not in ('public','guest')
Above query will return Username and its respestive login name. If Login Name returns as NULL, then it is termed as ORPHAN USER.
To remap the login with User, please follow the below URL
Please follow the below link for the solution
http://manage-sqlserver.blogspot.com/2012/08/remap-user-of-database-to-login-in-sql.html
Hope the above information helps
Thanks
