transfering logins and Passwords RRS feed

  • Question

  • Hi,

     I have migrated a database from sql server 2000 instance to another sql server 2000 instance.So i have taken the backup of the Tenu001 database and i have restored it onto the target instance.Then later on i tried to transfer the logins and passwords from the source instance to the target instance by executing the scripts from the following blogs/:http://support.microsoft.com/kb/918992/en-us,    http://support.microsoft.com/kb/246133,  http://blogs.techrepublic.com.com/howdoi/?p=140.So it showed me the message that the logins got transfered.Then later on i transferred the jobs of (Tenu001 Database) to the target instance by generating the scripts.So my problem is when i go to the target instance and check for the users it shows me that their logins have not yet been transferred.So they are orphaned users still existing.I have tried to run [sp_change_users_login]can fix issue but cannot resolve this problem.I had been trying hardly but cannot fix this issue.Please could any body give me a step by step procedure and code in detail.I am new to sql server.Please couls anybody give me the best solution to get rid of this problem.


    Thank You

    Wednesday, August 18, 2010 2:29 AM


All replies

  • The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

    This used to be a pain to fix, but currently (SQL Server 2000, SP3) there is a stored procedure that does the heavy lifting. All of these instructions should be done as a database admin, with the restored database selected.

    First, make sure that this is the problem. This will lists the orphaned users:

    EXEC sp_change_users_login 'Report'

    If you already have a login id and password for this user, fix it by doing:

    EXEC sp_change_users_login 'Auto_Fix', 'user'

    If you want to create a new login id and password for this user, fix it by doing:

    EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

    Friday, August 27, 2010 2:12 PM
  • Run the script from below url it will fix all the orphaned users instead of one by one




    Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.
    • Proposed as answer by Keshav Ramarao Thursday, September 2, 2010 8:39 PM
    • Marked as answer by Keshav Ramarao Wednesday, September 15, 2010 5:50 PM
    Monday, August 30, 2010 5:19 AM