Orphaned users

Here’s my crib-sheet for finding and dealing with orphaned users …

--orphaned_users.sql

-- SOURCE = 
-- TARGET = 

-- step-1: list orphaned users in current db
EXEC sp_change_users_login 'Report';

-- step-2: map user to login - if they both exist
EXEC sp_change_users_login 'update_one', 'some_user', 'some_login';

-- step-3: copy login, then step-2, then step-1
-- (NOTE: execute this on the source, then execute the output on the target)
Select
'Create Login ' + QUOTENAME(A.name) 
+ ' With Password=' + CONVERT(varchar(max), A.password_hash, 1) + ' hashed'		--script out the passwrod
+ ', DEFAULT_DATABASE=' + quotename(A.default_database_Name) --if you apply the scrip to a mirroed server or log-shipped server user master database, as user database is not in useable state
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
+ ', CHECK_POLICY=' + Case A.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End 
+ ', CHECK_EXPIRATION=' + Case A.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1)		--script out the SIDs
 As SQLLogin
From 
sys.sql_logins A
Where A.name Not like '##%##'  --remove those system generated sql logins
And A.sid != 0x01 --SA sid is always same
and QUOTENAME(A.name) = '[' + 'some_login' + ']'


-- Extra

-- list USERS (current server/db)
SELECT * FROM sys.database_principals where type in ('S', 'U') order by 1

-- list LOGINS (current server/db)
SELECT * FROM sys.server_principals where type in ('S', 'U') order by 1