Understanding and dealing with orphaned users in a SQL Server database

1 

2 

3 

1):
-- alter login cannot change sid, but re-create login can specify sid
ALTER LOGIN TestUser WITH   
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;  
GO 

-- CREATE LOGIN

-- DROP LOGIN
-- CREATE LOGIN with sid

2):
ALTER USER  WITH Login = ;
alter user [user_name] with login = [login_name] to fix orphaned windows login. 

1 

EXEC sp_validatelogins;  
GO
sp_validatelogins 

SELECT NAME,SID FROM SYS.DATABASE_PRINCIPALS
WHERE
TYPE = 'U' --Users only
AND AUTHENTICATION_TYPE = 3 --Windows Logins only
AND NAME NOT LIKE 'DBO'
EXCEPT
(SELECT DP.NAME, DP.SID FROM SYS.DATABASE_PRINCIPALS DP
INNER JOIN SYS.SERVER_PRINCIPALS SP ON DP.SID = SP.SID 
WHERE DP.TYPE = 'U'
AND DP.AUTHENTICATION_TYPE = 3)

3):
orphaned users 
2 
EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Auto_Fix', 'user'
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO
SELECT name, sid FROM sys.sql_logins WHERE name = 'TestLogin';  
GO 

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

USE adventureWorks
GO
sp_change_users_login @Action='Report'
GO

USE AdventureWorks
GO
--Command to map an orphaned user
sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

-- Command to map an orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

-- Command to map an orphaned user to a login that is not present but will be created
EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO