Transferring users from one db/server to another

[mattisontara]

I have created a replacement machine to take over for our old replicated server that we use for reporting. I have the new server replicating and am ready to begin switching the apps over to use the new server for reporting. I need to add the users from the old machine to the new machine. What are the various ways I can do this and what are the pros and cons? I know I can just do it manually (there are only about 12 users) but what if there were 100? Could I use DTS? If so, what should I check and uncheck to make sure I am only transferring users and permissions and not screwing up any data? Opinions and experience much appreciated.

 

[BobWman]

I have just gone through this - here are some of my notes, and the final procedure I decided to use.

NOTES:
If you select “Copy Tables…”, then drop the table, it may not be created the same (ie. No identity column, no primary key). So, the better selections are to select “Copy Objects…” or “Copy Tables…” and delete the data in the old table before copying(the identity values are copied over from the source table, interestingly). If you copy tables, you have to set the transformations for each table. If you copy objects, you only select the transformation options one time.

If you select "Copy Objects..." and you go with defaults on the "Select Objects To Copy" screen, will fail if users already exist – can’t add them. Must un-check “Copy all objects” and “Use default options”.

Procedure that seems to work
1. If you delete the data in the target tables, then use DTS to copy from the source, even the Identity column values are copied correctly. Problems occur if you drop a target table, then copy tables over.

2. Load the query DeleteAllTables into Transact SQL window. Load Enterprise manager. (I created this query to delete all the data from each table I want to transfer).

3. In Enterprise manager, right click on target database, and select All Tasks – Import Data. Click Next, and select the Source and target databases.

4. Select “Copy Objects and Data…” and click next.

5. Uncheck “Copy All Objects” and “Use Default Options”.

6. Click On select objects. Select all the tables that will be deleted by DeleteAllTables query. Click OK.

7. Click on Options. Uncheck “Copy Database Users…”. Click OK.

8. Don’t Uncheck “Drop Destination Objects First”. Click Next.

9. Run DeleteAllTables query.

10. Finish running the DTS routine in Enterprise Manager – all tables are transferred.

 

[mattisontara]

This sounds like instructions on how to copy DATA from one server to another. I already know how to do that. What I need to know is how to copy the users and permissions over. The database schemas are identical in terms of the tables. I just need to get the users over.