August 14, 2003 at 7:46 am
When I use the export wizard in SQL Server enterprise to copy a table from one server to another (test to production), I use the copy tables and objects. I select the table that I want to copy over. Now, if I don't deselect all the other options (copy primary keys, indexes, login permissions...) it will only copy the primary keys and indexes and permissions to the exact table that I'm copying, correct? It won't copy over the table I selected and also all the primary keys and login permissions of all the tables on the source server and overwrite all the indexes and permissions on the destination server. Just verifying because I think that my login rights on all my dest tables were changed when I copied just one table over.
August 18, 2003 at 12:00 pm
This was removed by the editor as SPAM
August 18, 2003 at 4:48 pm
If you selected "Copy object-level permissions" it would not do anything evil, but you must have the same logins set up in the source and destination databases. This will copy the permissions only on the object being copied.
If you select "Copy users and roles", you will add users and roles to the destination database. You get an error if the source db has users that do not have a login in the destination server. I don't know if it will overwrite an existing user, because I've never been brave enough to try this for fear of what I might have to clean up. If it did create a new user with the same name but a new userid, any permissions assigned to the old userid would be lost. I know if you use backup & restore to move a database from one system to another, the database users will be all screwed up unless you make sure to synchronize the login SIDs in both databases in advance.
If you select "Copy SQL logins" it will add (overwrite?) new login accounts to the server, which could affect all databases. Again, I'm not sure exactly what it does, I've never been brave enough to try it.
If I was setting up a new, empty system for backup or replication I might use the "Copy users" and/or "Copy logins" options, but otherwise I'd recommend staying away from them.
If I had to copy a table to another server and wanted to keep the permissions, I would use "Generate SQL Script" with all the options in Enterprise Manager to see exactly what I would get. Edit the script to make sure it does it correctly. You may have issues like placing the table and indexes in specific filegroups on production that don't exist on your test server, and the DTS copy wizard is just going to dump it all in PRIMARY. If you have a linked server set up from test to production, you can just add an INSERT/SELECT to the end of the script to copy the data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply