Post reply

DB users restore

  • August 15, 2008 at 9:36 am

    #123025

    All

    Hi i am doing now a days lot of cross database restores , it is getting very hard restoring users back to the database

    because the restore is from diffrent database not same as existing one.when i restore from it loosing all users and

    permissions

    do any one have best idea to restore users back with the same permissions.

    what are best proctices to do this kind of hetero restores.

    Thanks in advance

  • August 15, 2008 at 10:08 am

    #858849

    You are not telling how you are doing it right now so it's kind of hard to suggest something better.

    Lets go to the basics.

    LOGINS...

    ... belong to the instance therefore if you are restoring an user database you are not restoring LOGINS.

    If what you are looking to restore are LOGINS you have to script them on the source instance and apply scripts on the target one.

    USERS...

    ... belong to a database and they are restored when you restore the database but, if you do not have the related LOGIN in the target database you would run into troubles. These are also scriptable if needed.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 15, 2008 at 10:14 am

    #858852

    here's my suggestion:

    it sounds like it's just the users in the database from ServerA exist on ServerB where it gets restored, but the users are orphaned because the unique sid tied to "Bob" on ServerA is different on ServerB

    This happens all the time, and there is a script contribution here that helps resync users:AutoFix Orphaned Users[/url]

    so doing this manually, you'd simply restore the database, adn then run this script...

    if your restore is automated, just add this script to the end of the restoration process.

    If it is because AFTER the restore, you added users to access the db, and when you restore those users need to be re-added, there is another script to export the users and add them back:sp_GetUserInfo[/url]

    in that case, you'd need to export the users, then resotre the database, then run the results of the script to add them back.

    HTH

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 15, 2008 at 12:10 pm

    #858914

    Before restoring a database to overwrite an existing one, you need to save the information on the USERS in the existing database.

    After restoring the database, you need to erase the information on the USERS from the source database, apply then the data on the USERS saved before.

    Here is a linkd for mapping logins.

    http://support.microsoft.com/kb/918992/

    You can find the related approaches for mapping USERS.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply