Hi everyone,

I am copying a database from one machine to another.
The DB permissions are set for local users on each machine (including
IUSER_WEBSERVER1 and IUSER_WEBSERVER2 etc).

While copying the database I am using copy SQL Server Objects Task.
Under Copy Tab,
I am selecting create dest. objects (including drop destination objects
first, include all dependent objects, Include extended properties), copy data
(replace existing data) Use collation and Copy all objects.

Under default options I am not copying DB users and roles, sql server
logins, object level permissions since the users are diffenent on both
machines.

The basic aim is just transfer the data from one DB to another. I am having
hard time to manage the permissions.

Any thoughts about doing it efficiently without mapping permissions for the
objects i.e. keeping the permissions as is and just clean the data and copy
new / recent data from prod to staging.

Uday

By far the best way is to use BACKUP and RESTORE. In my expereince and a
lot of others this is the cleanest way.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Uday Deo" <UdayDeo (AT) discussions (DOT) microsoft.com> wrote

BACKUP and RESTORE won't solve the issue 'cos I DON'T want to copy the users,
roles and permissions. I just want to copy the data.

One way to go about it is to copy each table but then it requires to manage
the dependency as I can't copy the parent table before child table etc.

If someone knows that u can just overwrite the data it would be great.

What I can think of is

1. Create a SQL Script to remove the constraints
2. Create a SQL Script to add the constraints
3. Run the script for removing constraints on dest. DB
4. Copy the data over to each table of dest. DB from Source DB
5. Run the script for adding the constraints on dest. DB.

Step 1 and 2 can be once in a while task but I will have to put Step 3, 4
and 5 under a package and ensure that one runs after successful completion of
another.

Pl. let me know if this approach is useful.

Thanks,
Uday
3.

"Allan Mitchell" wrote:

Will the structure change?

If not then you may want to look at

1. Create a script that will drop DRI on the destination
2. Create a DTS package to pump all data from Source --> Destination
3. Apply DRI scripts

You can then permission each DB as you wish.

If the structure changes then you will need to apply those change using scripts.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Uday Deo" <UdayDeo (AT) discussions (DOT) microsoft.com> wrote

And another thing.

Why not BACKUP and RESTORE and then apply scripts to change any owners,
remove users etc etc



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Uday Deo" <UdayDeo (AT) discussions (DOT) microsoft.com> wrote