T-SQL: Remap SQL users

If you copy (backup/restore) a database from system A to system B, it is possible that the SQL users won’t be able login and this because the GUID/SID of the users on system A doesn’t match the GUID/SID of system B.

It is using the EXEC sp_change_users_login ‘Auto_Fix’, which inks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name.

This code will fix all users in the database that you are executing the SQL code. (I use this very often)

CREATE TABLE dbo.temp_OrphanUsers
        (
        UserName varchar(250) NULL,
        UserSID varchar(500) NULL
        )  ON [PRIMARY]

INSERT INTO dbo.temp_OrphanUsers
EXEC sp_change_users_login 'Report'

DECLARE curOU CURSOR FOR
        SELECT UserName FROM dbo.temp_OrphanUsers
OPEN curOU

DECLARE @OUUserName VARCHAR(250)
FETCH NEXT FROM curOU INTO @OUUserName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
        IF (@@FETCH_STATUS <> -2)
        BEGIN
                BEGIN TRY
                        EXEC sp_change_users_login 'Auto_Fix', @OUUserName
                END TRY
                BEGIN CATCH
                        PRINT 'Unable to auto-fix ' + @OUUserName + ' - User may not have a login'
                END CATCH
        END
FETCH NEXT FROM curOU INTO @OUUserName
END
CLOSE curOU
DEALLOCATE curOU

DROP TABLE temp_OrphanUsers
GO