how to resolve orphan users RRS feed

  • Question

  • hello guys i have sql server 2005...
    i restore db to sql server 2005..
    i found two users are orphan..
    though i dont have a login.. do u have a script to fix orphan user and create login automatically and map to those users..
    Friday, October 23, 2009 2:01 PM


  • Ok,

    From http://technet.microsoft.com/en-us/library/ms174378%28SQL.90%29.aspx

    The following example produces a report of the users in the current database that are not linked to any login and their security identifiers (SIDs).

    EXEC sp_change_users_login 'Report';

    The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login Mary does not exist.

    USE AdventureWorks;
    EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';


    Hope this helps.


    Friday, October 23, 2009 4:15 PM
  •   Just a quick reminder. sp_change_users_login has been deprecated, we strongly encourage to use ALTER USER DDL instead.

      -Raul Garcia
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Lekss Saturday, October 24, 2009 4:12 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, November 3, 2009 2:46 AM
    Friday, October 23, 2009 8:34 PM

All replies