Package Creator vs Owner and scheduled package failure

  • SQLGuy64

    Ten Centuries

    Points: 1051

    I have a package in MS SQL 2000 that was created by an Active Directory account that was deleted.  The scheduled job that invoked the package failed after the Acive Directory login was deleted.  The owner was changed to a valid Active Directory account using the code below before the Active Directory account was deleted. The scheduled job was not owned by the deleted Active Directory account

    from sp_reassign_dtspackageowner:

      --// Everything checks out, so reassign the owner.

      --// Note that @newloginname may be a sql server login rather than a network user,

      --// which is not quite the same as when a package is created.

      UPDATE sysdtspackages

        SET owner_sid = SUSER_SID(@newloginname),

         owner = @newloginname

     WHERE id = @id

      RETURN 0    -- SUCCESS

    Also we have noticed that everytime we go in and make a change to a package and save the creator become the owner again.

    Anybody have these issues?  Is the problem that the creator of the package is a deleted Active Directory login?  How can I change the creator?

    Thanx for your time,

    Andy

  • Jonathan Stokes

    SSCrazy Eights

    Points: 9861

    This is a known problem that...

    'UPDATE sysdtspackages

    SET owner_sid = SUSER_SID(@newloginname),

    owner = @newloginname

    WHERE id = @id'

    ...does not always fix becuase of the different versions of the package in the table.

    Sure fire way to fix is to save the package whilst logged on under the account that should now own it as a different savename. Then delete the package. Then save again as the original name. Delete the renamed.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • darren.green

    SSChasing Mays

    Points: 641

    The DTS package owner is irrelevent. It is only ever used when deleting or saving a package, such that you must be a sa or the owner to modify/delete.

    The owner of the scheduled job however is important, but this is a SQL login, and unrelated to DTS.

    An article that discusses package owners-

    Package Ownership Issues

    (http://www.sqldts.com/default.aspx?212)

    Darren Green
    SQLDTS.com   |   SQLIS.com   |   Konesans Ltd

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

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