November 10, 2005 at 9:58 am
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
November 10, 2005 at 10:03 am
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.
November 12, 2005 at 3:07 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply