Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Moving fro 2000 to 2008 R2

    I have setup a test server to migrate away from sql 2000 to 2008 R2 for our student info system. I did a backup on the source and did a restore on the destination and I can see all the tables and users, but it appears that a few users were orphaned specifically one user that the app that accesses the db needs to work. So I looked and it has no effective rights. So I did some searching and I ran the following command on the database I restored...

    Code:
    exec sp_change_users_login 'Report'
    It returns the five users, one of them is the one I need.

    I then ran this

    Code:
    EXEC sp_change_users_login 'Auto_Fix', 'schlUser'
    and got this in return

    Code:
    Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
    An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
    Just for fun I ran the same command above on the master and got this

    Code:
    The number of orphaned users fixed by updating users was 0.
    The number of orphaned users fixed by adding new logins and then updating users was 0.
    If it gives me the above message I assume the syntax is correct. So am I in the right direction? I have seen several solutions including one saying to restore the master from the source as loginsource but it failed to restore.

    I appreciate any help I can get!
    Thanks
    Alex

  2. #2
    Join Date
    Sep 2011
    Posts
    71
    The most widely suggested/used solution is to do a full back of SQL Server 2000 database and restore on SQL Server 2008 with no recovery. Then restore the subsequent transaction log backups with no recovery. When we are ready to switch, change SQL Server 2000 to read-only mode, backup the tail-log and restore it on SQL Server 2008 with recovery. Then bring SQL Server 2008 online.

    Migrating Your MS Access Database to MS SQL Server 7.0
    How to: Upgrade a SQL Server Failover Cluster Instance (Setup)

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    EXEC sp_change_users_login 'Update_One', 'schlUser', 'schlUser'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •