We can fix the orphaned users in SQL Server using “SP_CHANGE_USERS_LOGIN” stored procedure.
I have created one login as “login1” and it is mapped to one database “bujji” and having user at database level is ‘login1’. After some period of time suddenly login “login1” is deleted. Then immediately database level user ‘login1’ become orphaned user.
We can fix orphaned users using “sp_change_users_login” stored procedure.
Below figure it will clearly how to execute “sp_change_users_login”.
After running the script I found one orphaned user i.e login1 and its userSID. Simply we can create the login1 with same SID.
The following query it will tell clearly how to create login with SID.
create login <login-name> with password=”xxxxx”, sid= <sid>
create login login1 with password=’5657′,sid=0x9DD39AD747F3354E8BD0FE6BAC24160D