Find Orphan users from all databases exists in the SQL server

It is a typical requirement to find and fix the Orphan users in restored database to access the database with its underlying privileges.  In order to achieve this I have prepared a small script to find the Orphan users in all the databses exists in server.  Hope this helps to all, who are looking for this info in MS Sql server.

Script :

Declare @dbs table (id int identity(1,1), db varchar(100))

insert into @dbs (db)
select name from master.sys.databases where database_id > 4 order by name

--select * from @dbs


Declare @findOrphans table (db varchar(100), Orpuser varchar (100),USID varchar(200))
Declare @fixorphans table (db varchar(100), Script varchar (500))


Declare @a varchar(1000)
Declare @b tinyint
Declare @c tinyint
Declare @d varchar(100)

set @b = 1
select @c = MAX (id) from @dbs

while @b < @c
Begin
select @d = db from @dbs where id=@b
select @a = db+'.dbo.sp_change_users_login report ' from @dbs where id = @b
insert into @findorphans (Orpuser, UsID) exec (@a)
Update @findOrphans set db = @d where db is null
set @b = @b+1
end

select * from @findorphans

How to fix Orphan users in Sqlserver

The orphan users can be fixed by executing sp_change_users_login procedure. The syntax would be

Syntax :
sp_change_users_login 'update_one', 'Username','UserName'