TIP - To fix orphan uers

Orphan Users

When a database is moved from one server to another "Orphan users" is one of those little things that tend to create problem in smooth transition. 

There could be 2 reasons because of which Orphan users might get created

• Associated Login for the user itself isn't present on the server where database has been moved 
• Mismatch between SIDs of USER of the database and the LOGIN of the server

When the database user looses it's association (SID mismatch) with Login it becomes orphan and this uncoupling leads to LOGIN loosing its privileges on that database.

In this tip we will try to build a script to address second reason mentioned above

sp_change_users_login 'report' - lists the orphan users from the databases

master.sys.server_principals / master.dbo.syslogins - lists the logins present on the server. 

Usually when a user is created, username is kept same as that of login (although it can be different). we are going to build a script with an assumption that userName = LoginName. 

It will be 2 step process

• list the orphans users from the database
• associate them with the logins (with same name as that of user)

SET NOCOUNT ON 

DECLARE @query NVARCHAR(MAX)

SET @query = ''

IF OBJECT_ID('tempdb..#users','U') IS NOT NULL
DROP TABLE #users

CREATE TABLE #users
(
Username VARCHAR(1000),
userSID varbinary(85)
)

INSERT INTO #users(Username,userSID)
exec sp_change_users_login 'report'


SELECT @query = ISNULL(@query, '') + 'EXEC sp_change_users_login ''update_one'', '''  + Username + ''', '''  + Username + ''' ' + CHAR(10) --+ CHAR(13)
FROM #users u INNER JOIN master.dbo.syslogins sl
ON u.Username = sl.name


PRINT @query

--EXEC sp_executeSQL @query

Note - Currently, script lists the statements that can fix the orphan users. In case you want it fixed automatically then please remove comment(--) from the last line of the script.