Script to un-orphan users

• Ian Harris-306729

  Mr or Mrs. 500

  Points: 505

  More actions

  October 31, 2007 at 9:06 am

  #183538

  In configuring our logshipping I'm trying to setup a scripted method for un-orphaning the users. This script seems to work, but I'd like to know if it is a correct way of doing things :

  DECLARE @myvar sysname

  DECLARE testcursor CURSOR FOR

  select rtrim(name) from sysusers where issqlrole = 0

  open testcursor

  FETCH NEXT FROM testcursor INTO @myvar

  While (@@fetch_status = 0)

  begin

  exec sp_change_users_login 'update_one', @myvar

  FETCH NEXT FROM testcursor INTO @myvar

  end

  close testcursor

  deallocate testcursor

• binu john

  SSCommitted

  Points: 1987

  More actions

  October 31, 2007 at 8:56 pm

  #747308

  Just try this also

  SELECT

  'EXEC sp_change_users_login ''Update_One'', ''' + su.name + ''', ''' + su.name + ''''

  FROM

  sysusers su

  LEFT JOIN master.dbo.syslogins sl ON su.sid = sl.sid

  WHERE

  uid > 3-- exclude public, dbo, guest, INFORMATION_SCHEMA

  AND uid <> gid-- exclude groups

  AND uid < 16384-- exclude database roles

  AND sl.sid IS NULL-- user not linked to a login

  ORDER BY

  su.name