Fix orphaned users after restoring database to another server.

,

Run this script after restoring database to another server. Set restored database as a current database in the Enterprose Manager before running script in the query window.

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

Rate

3.8 (5)

Share

Share

Rate

3.8 (5)