Copy tables and permissions from one database table to another

Hi,

I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.

Thanks

If I were you I would script all the objects using management Studio and then execute those scripts against your new server/database.

-Jamie

|||

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

|||If your database is SQL Server, you can use a Transfer SQL Server Objects task in SSIS to move the objects. You can also use the Import/Export wizard (which generates SSIS packages) to accomplish the same thing.|||

Thread wrote:

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

You can use SSIS dataflows to move the data.

The CREATE TABLE scripts can be run from inside the SSIS package using the Execute SQL Task.

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

|||

Jamie Thomson wrote:

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

I've used it fairly successfully for simple, straightforward schema and data transfers. However, judging from the number of posts where people are having issues with it, I may be in the minority.

For more complex scenarios, I wouldn't recommend it.

|||

Hi jamie,

Thanks for your reply.

I've used the SSIS package.

It is working fine with small databases.

But in large database (More than 2 GB) having privileges and rolls on D/b it is not working, generating errors.

May be that was the problem came from the deadlock.

For more information:

I have few tables, few users, Few rolls, Few user defined data types.

Tables are naturally having primary key and foreign keys.

Thanks In Advance

Phani