How to Find and Fix Orphaned Users in SQL Server???

What is orphan user??
================
A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

what is a SID?
============ 
SID is short for “security identifier” A SID is an internal id which gets assigned to a server login when the login is created. The SID can be viewed by querying the Sys.server_principals system view or you can also view it by looking at sys.syslogins.

SELECT principal_id, sid, name FROM sys.server_principals;

SELECT sid, name FROM sys.syslogins;

How to Find SQL Server orphaned users??
=================================== 
There may be a number of orphaned users. So get the list of orphaned  user in your database you can execute below SP: 

USE DatabaseName --Replace DatabaseName  with actual database name
Go
EXEC sp_change_users_login 'Report';

Example:

USE TestVK
Go
EXEC sp_change_users_login 'Report';

How to Fix SQL Server orphaned users??
================================== 
If you already created a login which you want to map your database user to, you could run the below script: 

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';
GO

We have the database TestVK in which we got the orphaned user. Now suppose we have already a login "'LoginUser1" which we want to map. The we can execute the below code:

USE TestVK;
GO
EXEC sp_change_users_login 'update_one', 'User1', 'LoginUser1';

If we don’t have any login to map , in that case we can use sp_change_users_login to create one with a password. The following code does this and creates a login with the same name and a password of ‘Abc@321′ as an example.

EXEC sp_change_users_login 'Auto_Fix', 'User1', NULL, 'Abc@321#';

Thanks For Reading this Blog!!!