Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Moving users from one DB to another

    Is there a simple way to move users from one DB to another?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Relatively ... users have objects underneath. So, you probably don't want just to create user with the same name in your second DB which has nothing that your first user has. Therefore, you could try doing it through export/import utility.

    This works, I did it ... but, what might bother you, is moving them separately if they have something in common (foreign keys, for example). When you import one user, you'll receive numerous (let's say so) warnings about being unable to grant access to this table, that procedure, etc. etc. That is likely to happen for every user you move. It means that you'll have to restore everything later; having (for example, GRANT) scripts could help you much.

    If you (personally, working on a project or so) created those users and all objects they have (tables, procedures, etc.) and know relationships between them, you migh try moving them separately. That's a suicide (I did it; was not easy and took a long time). But, you know exactly which table to move first, to which user, which is next, etc.

    But, if those users are independent, I think that exp/imp will do the job for you.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What I do in those circumstances is to create the users on the new database
    then use export/import (fromuser=xxx touser=xxx) ... If you build the user
    accounts first, you do not get the errors on "unable to issue grant on table ..."

    Attached is a script that will build sql to create every user in a database ... run it from the existing database (spool output) ...

    HTH
    Gregg
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks for all the input. It was really valuable.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad it helped ...

Posting Permissions

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