How to "AUTO FIX" using alter user command?

Question

I was reading sp_change_users_login documentation and it says:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

How to auto_fix using alter user command?

Answer 1

You can fix all orphaned users comparing the user name in sys.database_principals with the login name in sys.server_principals that have mismatched SIDs

DECLARE @users NVARCHAR(MAX);

SET @users = '';

SELECT @users = @users + 'ALTER USER ' + b.name + ' WITH LOGIN = ' + a.name + ';'
FROM sys.server_principals AS a
INNER JOIN sys.database_principals AS b
     ON a.name = b.name
WHERE a.sid <> b.sid
      AND b.name <> 'public';

PRINT @users;

EXEC (@users);