Fix orphaned SQL users

Problem:

sometimes if you restore a SQL Server database from another server bak file, you may notice some database users without a mapping to an existing login. These users are called orphanded. To fix you can use the system stored procedure sp_change_users_login.

Solution:

You may find this script useful:

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;




NB: sp_change_users_login is deprecated and shall not be continued in further SQL Server versions.