"Joe Horton" <horj235 at lni dot wa dot gov> wrote in message =
news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:encxvv2bEHA.3512@TK2MSFTNGP12.phx.gbl...

"Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:Of1Hps6bEHA.384@TK2MSFTNGP10.phx.gbl...
> I agree with Andrew for your situation. Maybe the permissions issues are
> because you are trying to move a local login.
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:encxvv2bEHA.3512@TK2MSFTNGP12.phx.gbl...
> Joe,
>
> If you are getting all the data you would probably find it easier and
faster
> to do a restore from a full backup or an attach.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...
> Using the Copy SQL Server Objects Task inside a DTS package - I am
> attempting to refresh my DEV, TEST and Pre-Prod environments from my
> Production environment.
>
> I only want to refresh the data. This task seems best suited once I
click
> off all the dependancies such as copying the accompanied SP's.
>
> However; once I run the task (after it chugs and copies the data)- I get
> an error saying I don't have permissions. Once I investigate - I see the
> data copied across - but it appears it may be trying to drop the source
copy
> table or something.
>
> Isn't this task best suited to just copy data from one server to
another?
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:O6ROLY$bEHA.2972@TK2MSFTNGP12.phx.gbl...
> You do not need sa privileges to backup a database.
>
> from BOL
>
> Permissions
> BACKUP DATABASE and BACKUP LOG permissions default to members of the
> sysadmin fixed server role and the db_owner and db_backupoperator fixed
> database roles.
>
> In addition, the user may specify passwords for a media set, a backup set,
> or both. When a password is defined on a media set, it is not enough that
a
> user is a member of appropriate fixed server and database roles to perform
a
> backup. The user also must supply the media password to perform these
> operations. Similarly, restore is not allowed unless the correct media
> password and backup set password are specified in the restore command.
>
> Defining passwords for backup sets and media sets is an optional feature
in
> the BACKUP statement. The passwords will prevent unauthorized restore
> operations and unauthorized appends of backup sets to media using SQL
Server
> 2000 tools, but passwords do not prevent overwrite of media with the
FORMAT
> option.
>
> Thus, although the use of passwords can help protect the contents of media
> from unauthorized access using SQL Server tools, passwords do not protect
> contents from being destroyed. Passwords do not fully prevent unauthorized
> access to the contents of the media because the data in the backup sets is
> not encrypted and could theoretically be examined by programs specifically
> created for this purpose. For situations where security is crucial, it is
> important to prevent access to the media by unauthorized individuals.
>
> It is an error to specify a password for objects that were not created
with
> associated passwords.
>
> BACKUP creates the backup set with the backup set password supplied
through
> the PASSWORD option. In addition, BACKUP will normally verify the media
> password given by the MEDIAPASSWORD option prior to writing to the media.
> The only time that BACKUP will not verify the media password is when it
> formats the media, which overwrites the media header. BACKUP formats the
> media only:
>
> a.. If the FORMAT option is specified.
>
>
> b.. If the media header is invalid and INIT is specified.
>
>
> c.. If the operation is writing a continuation volume.
> If BACKUP writes the media header, BACKUP will assign the media set
password
> to the value specified in the MEDIAPASSWORD option.
>
> For more information about the impact of passwords on SKIP, NOSKIP, INIT,
> and NOINIT options, see the Remarks section.
>
> Ownership and permission problems on the backup device's physical file can
> interfere with a backup operation. SQL Server must be able to read and
write
> to the device; the account under which the SQL Server service runs must
have
> write permissions. However, sp_addumpdevice, which adds an entry for a
> device in the system tables, does not check file access permissions. Such
> problems on the backup device's physical file may not appear until the
> physical resource is accessed when the backup or restore is attempted.
>
>
>
>
>
> Yes the DBA is correct you can do this through DTS but the fact remains
this
> is a lot easier, quicker and sensible IMHO to do it through BACKUP and
> RESTORE.
>
>
>
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
>
> "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> news:eLXE9S$bEHA.3096@tk2msftngp13.phx.gbl...
> > I do not have SA privleges - in fact the DBA won't do it for us - he
> figures
> > we can do it with DTS.
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:Of1Hps6bEHA.384@TK2MSFTNGP10.phx.gbl...
> > > I agree with Andrew for your situation. Maybe the permissions issues
> are
> > > because you are trying to move a local login.
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > www.konesans.com - Consultancy from the people who know
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> > > news:encxvv2bEHA.3512@TK2MSFTNGP12.phx.gbl...
> > > Joe,
> > >
> > > If you are getting all the data you would probably find it easier and
> > faster
> > > to do a restore from a full backup or an attach.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> > > news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...
> > > Using the Copy SQL Server Objects Task inside a DTS package - I am
> > > attempting to refresh my DEV, TEST and Pre-Prod environments from my
> > > Production environment.
> > >
> > > I only want to refresh the data. This task seems best suited once I
> > click
> > > off all the dependancies such as copying the accompanied SP's.
> > >
> > > However; once I run the task (after it chugs and copies the data)- I
> get
> > > an error saying I don't have permissions. Once I investigate - I see
> the
> > > data copied across - but it appears it may be trying to drop the
source
> > copy
> > > table or something.
> > >
> > > Isn't this task best suited to just copy data from one server to
> > another?
> > >
> > >
> >
> >
>
>

"Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
news:eLXE9S$bEHA.3096@tk2msftngp13.phx.gbl...
> I do not have SA privleges - in fact the DBA won't do it for us - he
figures
> we can do it with DTS.
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:Of1Hps6bEHA.384@TK2MSFTNGP10.phx.gbl...
> > I agree with Andrew for your situation. Maybe the permissions issues
are
> > because you are trying to move a local login.
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > www.konesans.com - Consultancy from the people who know
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> > news:encxvv2bEHA.3512@TK2MSFTNGP12.phx.gbl...
> > Joe,
> >
> > If you are getting all the data you would probably find it easier and
> faster
> > to do a restore from a full backup or an attach.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> > news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...
> > Using the Copy SQL Server Objects Task inside a DTS package - I am
> > attempting to refresh my DEV, TEST and Pre-Prod environments from my
> > Production environment.
> >
> > I only want to refresh the data. This task seems best suited once I
> click
> > off all the dependancies such as copying the accompanied SP's.
> >
> > However; once I run the task (after it chugs and copies the data)- I
get
> > an error saying I don't have permissions. Once I investigate - I see
the
> > data copied across - but it appears it may be trying to drop the source
> copy
> > table or something.
> >
> > Isn't this task best suited to just copy data from one server to
> another?
> >
> >
>
>

"Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
news:u%23sEY3CcEHA.2504@TK2MSFTNGP12.phx.gbl...
> However, I only want to replace the data - nothing else - is really using
a
> backup/restore or even is that functionality avail to just do data only?
>
> In the meantime - I discovered why my Copy SQL Object task wasn't
working -
> I was trying to copy logins and users - didn't check the Options box - so
I
> can succesfully copy just data with this task now with DDL privleges.
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:O6ROLY$bEHA.2972@TK2MSFTNGP12.phx.gbl...
> > You do not need sa privileges to backup a database.
> >
> > from BOL
> >
> > Permissions
> > BACKUP DATABASE and BACKUP LOG permissions default to members of the
> > sysadmin fixed server role and the db_owner and db_backupoperator fixed
> > database roles.
> >
> > In addition, the user may specify passwords for a media set, a backup
set,
> > or both. When a password is defined on a media set, it is not enough
that
> a
> > user is a member of appropriate fixed server and database roles to
perform
> a
> > backup. The user also must supply the media password to perform these
> > operations. Similarly, restore is not allowed unless the correct media
> > password and backup set password are specified in the restore command.
> >
> > Defining passwords for backup sets and media sets is an optional feature
> in
> > the BACKUP statement. The passwords will prevent unauthorized restore
> > operations and unauthorized appends of backup sets to media using SQL
> Server
> > 2000 tools, but passwords do not prevent overwrite of media with the
> FORMAT
> > option.
> >
> > Thus, although the use of passwords can help protect the contents of
media
> > from unauthorized access using SQL Server tools, passwords do not
protect
> > contents from being destroyed. Passwords do not fully prevent
unauthorized
> > access to the contents of the media because the data in the backup sets
is
> > not encrypted and could theoretically be examined by programs
specifically
> > created for this purpose. For situations where security is crucial, it
is
> > important to prevent access to the media by unauthorized individuals.
> >
> > It is an error to specify a password for objects that were not created
> with
> > associated passwords.
> >
> > BACKUP creates the backup set with the backup set password supplied
> through
> > the PASSWORD option. In addition, BACKUP will normally verify the media
> > password given by the MEDIAPASSWORD option prior to writing to the
media.
> > The only time that BACKUP will not verify the media password is when it
> > formats the media, which overwrites the media header. BACKUP formats the
> > media only:
> >
> > a.. If the FORMAT option is specified.
> >
> >
> > b.. If the media header is invalid and INIT is specified.
> >
> >
> > c.. If the operation is writing a continuation volume.
> > If BACKUP writes the media header, BACKUP will assign the media set
> password
> > to the value specified in the MEDIAPASSWORD option.
> >
> > For more information about the impact of passwords on SKIP, NOSKIP,
INIT,
> > and NOINIT options, see the Remarks section.
> >
> > Ownership and permission problems on the backup device's physical file
can
> > interfere with a backup operation. SQL Server must be able to read and
> write
> > to the device; the account under which the SQL Server service runs must
> have
> > write permissions. However, sp_addumpdevice, which adds an entry for a
> > device in the system tables, does not check file access permissions.
Such
> > problems on the backup device's physical file may not appear until the
> > physical resource is accessed when the backup or restore is attempted.
> >
> >
> >
> >
> >
> > Yes the DBA is correct you can do this through DTS but the fact remains
> this
> > is a lot easier, quicker and sensible IMHO to do it through BACKUP and
> > RESTORE.
> >
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > www.konesans.com - Consultancy from the people who know
> >
> >
> > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> > news:eLXE9S$bEHA.3096@tk2msftngp13.phx.gbl...
> > > I do not have SA privleges - in fact the DBA won't do it for us - he
> > figures
> > > we can do it with DTS.
> > >
> > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > news:Of1Hps6bEHA.384@TK2MSFTNGP10.phx.gbl...
> > > > I agree with Andrew for your situation. Maybe the permissions
issues
> > are
> > > > because you are trying to move a local login.
> > > >
> > > > --
> > > > --
> > > >
> > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > www.konesans.com - Consultancy from the people who know
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> > > > news:encxvv2bEHA.3512@TK2MSFTNGP12.phx.gbl...
> > > > Joe,
> > > >
> > > > If you are getting all the data you would probably find it easier
and
> > > faster
> > > > to do a restore from a full backup or an attach.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message
> > > > news:OWMOhr2bEHA.1356@TK2MSFTNGP09.phx.gbl...
> > > > Using the Copy SQL Server Objects Task inside a DTS package - I am
> > > > attempting to refresh my DEV, TEST and Pre-Prod environments from my
> > > > Production environment.
> > > >
> > > > I only want to refresh the data. This task seems best suited once
I
> > > click
> > > > off all the dependancies such as copying the accompanied SP's.
> > > >
> > > > However; once I run the task (after it chugs and copies the data)-
I
> > get
> > > > an error saying I don't have permissions. Once I investigate - I
see
> > the
> > > > data copied across - but it appears it may be trying to drop the
> source
> > > copy
> > > > table or something.
> > > >
> > > > Isn't this task best suited to just copy data from one server to
> > > another?
> > > >
> > > >
> > >
> > >
> >
> >
>
>