sql server dts: Best way of user copying database on same server -- sql server dts

Best way of user copying database on same server

brcw2 NO[at]SPAM yahoo.com
1/6/2005 6:31:44 AM

sql server dts: We have a situation where we having a training database on the same
instance as the live database. At irregular intervals, a user needs
to copy recent live data over on to the training database. In the
past, this has been done by the user restoring the latest backup over
the training database, but this requires them to have sys admin rights
on SQL, which I'm rescinding.

I've tried using DTS, but haven't yet figured it out - the closest
I've got was using the import/export wizard, but using "copy objects
and data" means all the new objects have the user as the owner instead
of dbo.

Any suggestions as to a sensible way of setting this up?

Many thanks

Re: Best way of user copying database on same server

The Margolins
1/9/2005 2:48:35 PM

Bernard,

User security can be limited to db_owner or db_backupoperator fixed database
roles to be able to backup. For restore CREATE DATABASE permissions are
needed for brand new database and dbcreator fixed server role and the owner
(dbo) of the database for existing database. See permissions section in:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

Ilya

[quoted text, click to view]