DTS permissions and ownership

We have a DTS package developed on our development PC's (SQL 7). It
runs fine. When we schedule it on the server (SQL 7), it fails. We
have been able to find that this is a known issue down to the use of
permissions (running it uses the users permissions, scheduling it uses
the servers). http://support.microsoft.com/?kbid=269074

Our Ops Support team insist that we have the full permissions to run
this, but it still fails. If we log on to the server as the service
and re-create (sample) the DTS package, we can schedule it. However,
we don't want to have to go the the comms room (or use VNC) to develop
DTS packages. If we then change this on our development PC's we get
the same problem as we appear to have taken ownership. It looks like
ownership is not just on the DTS package, but on the components as
well.

Is there are way of changing the ownership of the DTS package /
components ?

What can I check with regards permissions ?

Any help would be appreciated.

Thanks

RyanManaged to find the answer. It was down to a different version of
MDAC.
Ver 2.5 was on the server and 2.6 was on the development PC's. With
the connection properties, additional information is passed with 2.6
which causes a problem with 2.5

When executing the DTS package, it uses the current PC's version. When
scheduled, it uses the version on the server.

The way around this was to re-do the connection properties while
logged on the server (using VNC) and then save it.

Now we know what needs doing, we'll get all of the PC's & servers
checked and put on the same version.

R

ryanofford@.hotmail.com (Ryan) wrote in message news:<7802b79d.0404190132.4438ecc2@.posting.google.com>...
> We have a DTS package developed on our development PC's (SQL 7). It
> runs fine. When we schedule it on the server (SQL 7), it fails. We
> have been able to find that this is a known issue down to the use of
> permissions (running it uses the users permissions, scheduling it uses
> the servers). http://support.microsoft.com/?kbid=269074
> Our Ops Support team insist that we have the full permissions to run
> this, but it still fails. If we log on to the server as the service
> and re-create (sample) the DTS package, we can schedule it. However,
> we don't want to have to go the the comms room (or use VNC) to develop
> DTS packages. If we then change this on our development PC's we get
> the same problem as we appear to have taken ownership. It looks like
> ownership is not just on the DTS package, but on the components as
> well.
> Is there are way of changing the ownership of the DTS package /
> components ?
> What can I check with regards permissions ?
> Any help would be appreciated.
> Thanks
>
> Ryansqlsql

DTS PACKAGE OWNERSHIP CHANGE

Can someone please tell me how to easily change the
ownership of a DTS package from the person who created
them to any other login on the server? Thank you.
RandaIn message <1f9501c3e103$6464b960$a301280a@.phx.gbl>, Randa
<anonymous@.discussions.microsoft.com> writes

quote:

---

>It worked just fine and I really appreciate. However, I
>was hoping to do several at a time, I tried it with the IN
>operator and it didn't work - I had an "Incorrect syntax
>near the keyword 'in'" Is there a way of doing more than
>one at a time? Thank you
>Here is my script:
>Exec sp_reassign_dtspackageowner
>@.name in ('NewaPackDB','clientDB'),
>@.id in ('C2C1BBD2-73...','C2C1BBD2-73...'),
>@.newloginname = 'BA\adm'
>

---

There is a script here that does this, but please read the limitations
of this. It is only temporary as it doesn't change the owner in the
underlying package object.
Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

DTS PACKAGE OWNERSHIP CHANGE

Can someone please tell me how to easily change the
ownership of a DTS package from the person who created
them to any other login on the server? Thank you.
RandaIn the msdb database there is stored procedure named
sp_reassign_dtspackageowner that will do the trick. Here's
some sample syntax:
msdb..sp_reassign_dtspackageowner
@.name = 'Package Name',
@.id = 'FF18ACDD-3FBF-48FF-8E1D-0CE4038D5EE4',
@.newloginname = 'sa'
You can obtain the package ID (@.id) from the
msdb..sysdtspackages table (ID column).
EXAMPLE:
First get the id:
Select id from dbo.sysdtspackages
where name = 'MYDTSPACKAGE'
GO
Adjust the syntax
Exec sp_reassign_dtspackageowner
@.name = 'MYDTSPACKAGE',
@.id = 'D72333A2-7DED-11D4-BF04-00B0D057DBE4',
@.newloginname = 'Userid'
Hope it helps......
>--Original Message--
>Can someone please tell me how to easily change the
>ownership of a DTS package from the person who created
>them to any other login on the server? Thank you.
>Randa
>.
>|||It worked just fine and I really appreciate. However, I
was hoping to do several at a time, I tried it with the IN
operator and it didn't work - I had an "Incorrect syntax
near the keyword 'in'" Is there a way of doing more than
one at a time? Thank you
Here is my script:
Exec sp_reassign_dtspackageowner
@.name in ('NewaPackDB','clientDB'),
@.id in ('C2C1BBD2-73...','C2C1BBD2-73...'),
@.newloginname = 'BA\adm'
>--Original Message--
>In the msdb database there is stored procedure named
>sp_reassign_dtspackageowner that will do the trick.
Here's
>some sample syntax:
>msdb..sp_reassign_dtspackageowner
>@.name = 'Package Name',
>@.id = 'FF18ACDD-3FBF-48FF-8E1D-0CE4038D5EE4',
>@.newloginname = 'sa'
>You can obtain the package ID (@.id) from the
>msdb..sysdtspackages table (ID column).
>
>EXAMPLE:
>First get the id:
>Select id from dbo.sysdtspackages
>where name = 'MYDTSPACKAGE'
>GO
>Adjust the syntax
>Exec sp_reassign_dtspackageowner
>@.name = 'MYDTSPACKAGE',
>@.id = 'D72333A2-7DED-11D4-BF04-00B0D057DBE4',
>@.newloginname = 'Userid'
>
>Hope it helps......
>
>>--Original Message--
>>Can someone please tell me how to easily change the
>>ownership of a DTS package from the person who created
>>them to any other login on the server? Thank you.
>>Randa
>>.
>.
>|||In message <1f9501c3e103$6464b960$a301280a@.phx.gbl>, Randa
<anonymous@.discussions.microsoft.com> writes
>It worked just fine and I really appreciate. However, I
>was hoping to do several at a time, I tried it with the IN
>operator and it didn't work - I had an "Incorrect syntax
>near the keyword 'in'" Is there a way of doing more than
>one at a time? Thank you
>Here is my script:
>Exec sp_reassign_dtspackageowner
>@.name in ('NewaPackDB','clientDB'),
>@.id in ('C2C1BBD2-73...','C2C1BBD2-73...'),
>@.newloginname = 'BA\adm'
>
There is a script here that does this, but please read the limitations
of this. It is only temporary as it doesn't change the owner in the
underlying package object.
Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

DTS Package Owner

Hello there,

Im trying to change the owership of the DTS package but am a little
confused.

sp_changeobjectowner changes the ownership of table, view, or stored
procedure in the current database...

How could i change the DTS package ownership?

Thank you in advance.

Leo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo

In most cases, it doesn't really matter who owns the package - who is
executing it is more important. However, there is an undocumented
stored procedure called msdb..sp_reassign_dtspackageowner, which you
can use.

You might want to look at http://www.sqldts.com - in the
Administration and Management section of the FAQ, there is an article
on package ownership issues, including a wrapper procedure for
sp_reassign_dtspackageowner.

Of course, since this procedure is undocumented, you should be careful
using it, and preferably never in production code - its behaviour may
change in future releases with no warning.

Simon|||Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Hi Leo, use the undocumented SP:

sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'

[@.name =] 'name'
The package name.

[@.id =] 'id'
This is the uniqueidentifier for the package. A name may not
necessarily be unique.

[@.newloginname =] 'newloginname'
The new Owner name. SQL Server login example 'sa', NT Integrated
example 'Domain\Username'

Regards,
Thomas
http://www.sqlscripter.com