DTS permissions?

i posted this in programming but nobody answered so I thought I might try he
re aswell.
Can anyone help us with the following...
we've got lots of DTS packages stored on SQL server, however we have noticed
that it takes the login credentials for what to save the DTS package owner
as from windows, and not from the SQL server login that you type in to save
it. EVEN though we have got
the SQL server installation set to not allow trusted connections.
What we are trying to do is get it so we can save each other's DTS packages
without being members of the sysadmin role. Is there anyway we can do it?
The dba (rightly) doesn't want to give us all sa rights, and we can't switch
over to windows authentication until we get our own domain (which is months
off), as otherwise we would have to give the IT department's domain adminis
trators access to our serve
rs, which we don't want to do.
Any other way round it?
The dba's just come up with some 'undocumented stored procedure' that might
enable us to change things in some way but he's going to let me have a look
after lunch, but has anybody else experienced this problem?BOL refers:
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the
DTS Run utility, or from the command prompt executes under the security cont
ext of the user who is currently logged in. However, a package scheduled for
execution runs under the s
ecurity context of the SQL Server Agent job that runs the package. The owner
of that job may or may not be the same as the user currently logged in.
For packages created under a Microsoft Windows NT? 4.0 or Microsoft Windows
? 2000 account, the job runs under the security context of the account that
started SQL Server Agent.
If the job is owned by a login belonging to the sysadmin fixed server role,
the security context of the package defaults to the account used to start th
e local SQL Server Agent. If the server is registered using Windows Authenti
cation, the owner of the jo
b is the account of the SQL Server Agent. If the server is registered using
SQL Server Authentication, the owner of the job is that SQL Server login.
If the job is owned by a login that is not a member of the sysadmin fixed se
rver role, the package runs under the context of the job step proxy account,
with the rights and permissions of that account.
--
Satya SKJ
"Beeeeeeeeeeeeves" wrote:

> i posted this in programming but nobody answered so I thought I might try
here aswell.
> Can anyone help us with the following...
> we've got lots of DTS packages stored on SQL server, however we have noticed that
it takes the login credentials for what to save the DTS package owner as from window
s, and not from the SQL server login that you type in to save it. EVEN though we hav
e g
ot the SQL server installation set to not allow trusted connections.
> What we are trying to do is get it so we can save each other's DTS package
s without being members of the sysadmin role. Is there anyway we can do it?
> The dba (rightly) doesn't want to give us all sa rights, and we can't switch over
to windows authentication until we get our own domain (which is months off), as othe
rwise we would have to give the IT department's domain administrators access to our
ser
vers, which we don't want to do.
> Any other way round it?
> The dba's just come up with some 'undocumented stored procedure' that migh
t enable us to change things in some way but he's going to let me have a loo
k after lunch, but has anybody else experienced this problem?
>|||Not helpful, please don't just quote BOL.
Re-read the question and re-consider whether your answer comes even vaguely
close to answering it, in case you didn't work it out, the answer is no.
The question was HOW CAN WE SAVE EACH OTHER'S PACKAGES. I also clearly state
d that the server is NOT windows authentication, and the users that the prob
lem affects are NOT members of the sysadmin role. Yet you chose to rabble on
(or copy and paste text th
at rabbles on) about "if you are using windows authentication" and "if you a
re in the sysadmin role".
The answer seems to be to save it in metadata services rather than local pac
kages.|||"When saving a local package Enterprise Manager calls sp_add_dtspackage
found in the msdb database. If you are not the owner and not a member of the
sysadmin role an error will be returned. If you fail this check the error
"Only the owner of DTS Package 'MyPackage' or a member of the sysadmin role
may create new versions of it." will be returned. A similar check is
contained within sp_drop_dtspackage which is used when deleting a package.
If multiple non-sysadmin developers all need to work on the same package
this limitation can cause major problems. One totally unsupported workaround
would be to remove the checks from the stored procedure. I have implemented
this successfully in development environments where teams all need to work
with the same packages, without any problems. "
The undocumented proc will sp_reassign_dtspackageowner, but that is no use
in a dev shop, as it bneeds running each time a package has been saved and a
new user wants access.
Package Ownership Issues
(http://www.sqldts.com/default.aspx?212)
Darren Green
http://www.sqldts.com
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:65E2ED1D-0810-4044-B8EE-342171657FB8@.microsoft.com...
> Not helpful, please don't just quote BOL.
> Re-read the question and re-consider whether your answer comes even
vaguely close to answering it, in case you didn't work it out, the answer is
no.
> The question was HOW CAN WE SAVE EACH OTHER'S PACKAGES. I also clearly
stated that the server is NOT windows authentication, and the users that the
problem affects are NOT members of the sysadmin role. Yet you chose to
rabble on (or copy and paste text that rabbles on) about "if you are using
windows authentication" and "if you are in the sysadmin role".
> The answer seems to be to save it in metadata services rather than local
packages.
>|||Ah ... does it! cheers. I'll check that out as a potential loophole as all u
s devs have been made dbo of msdb.
Cheers
"Darren Green" wrote:

> "When saving a local package Enterprise Manager calls sp_add_dtspackage
> found in the msdb database. If you are not the owner and not a member of t
he
> sysadmin role an error will be returned. If you fail this check the error
> "Only the owner of DTS Package 'MyPackage' or a member of the sysadmin rol
e
> may create new versions of it." will be returned. A similar check is
> contained within sp_drop_dtspackage which is used when deleting a package.
> If multiple non-sysadmin developers all need to work on the same package
> this limitation can cause major problems. One totally unsupported workarou
nd
> would be to remove the checks from the stored procedure. I have implemente
d
> this successfully in development environments where teams all need to work
> with the same packages, without any problems. "
> The undocumented proc will sp_reassign_dtspackageowner, but that is no use
> in a dev shop, as it bneeds running each time a package has been saved and
a
> new user wants access.
> Package Ownership Issues
> (http://www.sqldts.com/default.aspx?212)
>
> --
> Darren Green
> http://www.sqldts.com
> "Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
> message news:65E2ED1D-0810-4044-B8EE-342171657FB8@.microsoft.com...
> vaguely close to answering it, in case you didn't work it out, the answer
is
> no.
> stated that the server is NOT windows authentication, and the users that t
he
> problem affects are NOT members of the sysadmin role. Yet you chose to
> rabble on (or copy and paste text that rabbles on) about "if you are using
> windows authentication" and "if you are in the sysadmin role".
> packages.
>
>