Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Restoring Database MSSQL 2000

    Hi All

    We restored a database but the user asscociated with that database is not being restored. Is there a way to do this?

    Question: Is there a way to restore the complete database including the users.

    Thanks

    James

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    This happens because the login belongs to the master database while the user belongs to the database that was restored (maybe that's an oversimplification, but that's how I approach it).

    There are a couple of things that you can do:
    1. If there are just a few users, add the user (but don't associate it to the database). Then login to the database using Query Analyzer and run exec master.dbo.sp_change_users_login 'UPDATE_ONE', '[login]', '[login]'. This will re-associate your database user's SID with the SID for the master database login.

    2. If there are many users, you can use the Transfer Logins task in DTS to transfer the logins from the original server (assuming its still there) to the new server. Then run sp_change_users_login in the automatic mode (see BOL).

    3. Finally, you can bcp out the contents of the syslogins table. There's a utility somewhere (I can't remember where right now) to then re-import that data into the target server's syslogins table. Again, you will still have to run sp_change_users_login.

    Or, you could just delete and recreate the user(s); but then you have to re-assign all the privileges/etc.

    HTH and good luck,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Also

    In the last option as hmscott mentioned. Deleting users may need deleting from system table itself as it sometimes doesn't show users of restored database in enterprise manager

    sp_configure "allow updates" , 1
    go
    Delete from db_name..sysusers where...
    go
    sp_configure "allow updates" , 0

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Lightbulb

    Put these two procedures in the master database on the source server. Run sp_help_revlogin in the master database on the source server. Then add the users appropriately on the destination server by running the script produced in the master database on the destination server. This will map the SID correctly for you so you don't have to monkey around with sp_change_users or dropping/recreating users.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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