ORPHANED USERS? HOW TO FIXED ORPHANED USERS?

When a DB is move, attach & detach, restoring to different instance or corresponding login is dropped, the users of the Database becomes Orphaned users and they can’t access the database because the database users are not mapped to SID (Security Identifier) in the new instance.

To detect Orphaned users:- EXEC sp_change_users_login 'Report'

Fix the Orphaned users : EXEC  sp_change_users_login 'Auto_Fix',@tempUsername;

If multiple Orphaned users then :

CREATE TABLE #OrphanedUsers(

row_num  INT IDENTITY(1,1),

username VARCHAR(1000),

id       VARCHAR(1000)

)

INSERT INTO #OrphanedUsers(username,id)

EXEC sp_change_users_login 'Report'

DECLARE @rowCount INT = (SELECT COUNT(1) FROM #OrphanedUsers );

DECLARE @i INT =1 ;

DECLARE @tempUsername VARCHAR(1000);

WHILE(@i <= @rowCount)

BEGIN

               SELECT @tempUsername = username FROM #OrphanedUsers WHERE row_num = @i;

               EXEC  sp_change_users_login 'Auto_Fix',@tempUsername;

               SET @i = @i+1;

END

DROP TABLE #OrphanedUsers;