November 9, 2006 at 7:10 am
Greetings all,
I'm taking over from a very good DBA/Developer who has created a large number of DTS packages.
These log in using his login ID, BUT...his entire NT account is also being deleted. I am somewhat concerned about the SID implications, and fear they may not be able to complete once his account is deleted this weekend.
My questions are, how can I query his packages en masse to determine whether they indeed are dependant on his own login, and if so, how can I safely alter them to use another login?
Best regards,
Jaybee.
November 10, 2006 at 6:20 am
Things to consider...
1) how are the dts making their connections? If they are using standard SQL OLE connections, then you would need to check each one and hope they are using SQL authenications. If they are using UDL, then there is probably a single connection to change.
2) dropping the NT account will prevent the execution of the DTS provided that it was not using the NT account for authenications, it would only prevent you from making "changes" to the dts if you don't have sysadmin rights.
NEVER use NT accounts when creating objects in SQL. ALWAYS use groups, whether is an NT group or SQL group (ie login). It makes things easier when someone leaves and their account get deactivated.
Good luck
November 10, 2006 at 9:16 am
If you want to check a large number of packages, it may be worthwhile to use VB.NET with the DTSPackage Object Library. Enumerate the packages on a server (or package files in a folder), load each package into a package object, enumerate the connections collection, and check the properties of each connection.
I'm not positive if you can also change the package owner through the object model, but you can use the sp_reassign_dtspackageowner procedure in msdb.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply