Re: DTS permissions?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Beeeeeeeeeeeeves (Beeeeeeeeeeeeves_at_discussions.microsoft.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 05:57:04 -0700

Ah ... does it! cheers. I'll check that out as a potential loophole as all us 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 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.
> >
> >
>
>
>

Relevant Pages

  • Re: DTS permissions?
    ... I'll check that out as a potential loophole as all us devs have been made dbo of msdb. ... > If multiple non-sysadmin developers all need to work on the same package ...
    (microsoft.public.sqlserver.security)
  • Re: [gutsy] second dpkg error in one day
    ... is there anything the devs can do to avoid this? ... package to another. ... Because of the nature of mirroring, ... even if the developer could guarantee it on the initial site. ...
    (Ubuntu)
  • Re: Saving SSIS Packages
    ... I tried using the import wizard, importing a file based package and migrating ... Is DTC required for importing into msdb? ... When using the import wizard in SQL Server Management Studio, ... Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, ...
    (microsoft.public.sqlserver.dts)
  • Re: How do I grant execute/edit for a DTS package?
    ... If you choose Structured Storage File, it will ask you for a File Name ... contents of the package rather than having one person with rights. ... If the file already exists in SQL Server (in the msdb database), simply edit ...
    (microsoft.public.sqlserver.dts)
  • Re: Existence of Package.
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... SysDtsPackages but this needs a permission on msdb. ... before trying to load the Package. ...
    (microsoft.public.sqlserver.dts)