DTS Package Ownership

  • matey

    SSCommitted

    Points: 1785

    Hi Guys,

    What is the easiest way to change DTS package owner? Any response much appreciated.

    Cheers

    A

  • RKMic

    SSC Veteran

    Points: 292

    Check out this link http://www.sqlservercentral.com/scripts/contributions/563.asp

    "Changing the Owner of a Single DTS Package"

    This is what I use for changing ownership.

  • Greg Charles

    SSC-Forever

    Points: 45403

    You can change the owner without using the stored procedure and cursor.  The key bit is the undocumented stored procedure sp_reassign_dtspackageowner.

    In msdb, Execute sp_enum_dtspackages to find the name and id of the package you want to change ownership of.  Then execute sp_reassign_dtspackageowner:

    EXEC sp_reassign_dtspackageowner @name = name, @id = id,  @newloginname = newowner

    where newowner is a domain login, not a SQL Server login.

    Greg

    Greg

  • ejohnso5

    SSC Enthusiast

    Points: 131

    I know this post is old, but I wanted to add something I have had to do in SQL 2005.

    After using sp_reassign_dtspackageowner, I had sucessfully changed the owner, but my user still could not save/create new versions of the DTS.

    I found that in msdb.sysdtspackages, her login name was in 'owner' correctly but not in 'owner_sid'. So what I had to do was retrieve her sid from msdb.sysusers, and then execute this one-

    update sysdtspackages set owner_sid = her sid where owner like 'Her Login';

    Now she can edit and save DTS packages.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply