Orphan Users
Get all orphan users and change the there logins by looping on the temporary table.
Last updated:
Warning: Review and test in a non-production environment before running.
Declare @ExecStr varchar(256)
Declare @UserName varchar(256)
Create table #Orphanage(UserName varchar(256))
insert #Orphanage(UserName)
select UserName = name
from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
DECLARE Adopt_them CURSOR FOR
select UserName from #Orphanage
OPEN Adopt_them FETCH NEXT FROM Adopt_them into @UserName
WHILE @@FETCH_STATUS = 0
begin
set @ExecStr='EXEC sp_change_users_login ''UPDATE_ONE'', ''' +
@UserName +''',''' +
@UserName +'''' execute(@ExecStr) print @UserName +
' Updated'
FETCH NEXT FROM Adopt_them into @UserName
end
CLOSE Adopt_them
DEALLOCATE Adopt_them