Find SQL Orphan Users and Fix using ALTER USER 

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

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_principalswhere 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 JOINsys.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, Please use version 1 in that case. 
WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principalswhere type = 'S'
)
select *,'ALTER USER '+a.name+' WITH LOGIN ='+''''+a.name+'''' As Fixusers from sys.server_principalsa JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name


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.