Resolve orphan user issue.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  March 2, 2010 at 7:01 am

  #139107

  Generally to resolve the orphan users issue, I follow the below steps:

  (1) sp_change_users_login 'report' -> Execute the query in the database.

  (2) sp_change_users_login 'update_one', 'login_name','user_name' ===> to match sid value of syslogins and sysusers.

  But, if I have many orphan users available in a database, then manually executing step 2, will consume more time. Instead of this, do we have any single command which we can execute to fix all the 'orphan users' together? Please suggest..

  Thanks.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736445

  More actions

  March 2, 2010 at 7:05 am

  #1127050

  You can use the auto_fix option - http://technet.microsoft.com/en-us/library/ms174378.aspx

  Or write a script based on the output of step 1 to execute things for you.