Find Orphan SQL Users and Fix using ALTER USER

For years i have been using syslogins and sysusers tables in a cursor to find orphan users and sp_change_users_login to fix orphan users in SQL Server database.

With sp_change_users_login depreciated from SQL 2014 and with new system objects post SQL server 2005 I thought i would be a good time to post the new version of the TSQL i follow.

I have 2 versions of query to find Orphan users in a SQL database,

Version 1:-
 

WITH login_CTE (name, type, sid)

As
(
Select name, type, sid from sys.database_principals where type = 'S' AND name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA','sys')
)
select a.*,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from login_CTE a
LEFT JOIN sys.server_principals b ON a.sid = b.sid
where b.name IS NULL

Version 2:-

Note:- This version had some issues with databases with non standard collation to master database, So I have used the COLLATE statment to match the collation. 

WITH login_CTE (name, type, sid)

As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select *,'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name COLLATE Latin1_General_CI_AS
GO

Both versions will will have a column with name Fixusers with the ALTER USER statement instead of sp_change_users_login to fix the orphan users. Just copy the column and execute the query to fix all the orphan users in the database.

Eg: ALTER USER TestLogin1 WITH LOGIN =TestLogin1

Note:- This query is database specific and needs to executed in all databases required to be fixed.