sp_change_users_login ‘auto-fix’, ”

How can I execute the sp_change_users_login so that it will auto-fix all local sql accounts?

in other words I can run the command to view all local accounts:

select * from sys.database_principals
where type = 's';

I now want those list of users to be used in the sp_change_users_login procedure.

Answer

You could use a cursor to get the list of names, and then loop through the cursor results. Within the loop you would do something like: exec sp_change_users_login ‘auto-fix’, @nameVariable

You’d loop while @@FETCH_STATUS = 0. It returns non-zero when you have FETCHed all the rows from the cursor.

Here’s more detail:

declare @userVar varchar(30)
declare users cursor for select name from sys.database_principals where type = 's';

open users
fetch next from users into @userVar
while @@FETCH_STATUS = 0
begin
    exec sp_change_users_login 'auto_fix', @userVar
    fetch next from users into @userVar
end


close users
deallocate users