0

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?

1 Answer 1

2

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);
3
  • Use STRING_AGG (or FOR XML PATH pre-SQL 2017) instead of @users = @users + .... The behavior of aggregate string concatenation is otherwise undefined.
    – Dan Guzman
    Dec 12, 2022 at 21:45
  • Not sure, but should we exclude 'guest', 'sa', 'dbo', 'public', 'sys' and 'INFORMATION_SCHEMA' users? your script only exclude public.
    – Potter
    Dec 13, 2022 at 21:50
  • all of them except sa, are not logins on the instance thus shouldnt match to a name in sys.server_principals. as for sa, if you have created the user sa in the database then yes, you would need to exclude. I dont create sa users specifically as the login already has full permissions to all database.
    – Bob Klimes
    Dec 14, 2022 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.