May 6, 2004 at 9:07 am
Is there any script that can retrieve for me the username and password used by developers to create their packages. I can't go thru 600 DTS to check their usernames.
Thanx in advance
May 6, 2004 at 12:22 pm
If, by username, you mean the owners of the packages, you can SELECT OWNER FROM MSDB.DBO.SYSDTSPACKAGES.
There is no way to retrieve the owner password.
Greg
Greg
May 7, 2004 at 12:45 am
Maybe let me re-phrase my question, i need to retrieve the server, username of the their connection in the DTS-Package. That is if the Connect into SQL sever, i need the script that will help me to check which servers were they connecting to and which username were they using, password is not important
May 7, 2004 at 3:25 pm
I don't have the code at my fingertips, but I know how to approach the problem (sounds like a job interview, right?).
You can cycle through the collection of DTS packages, and you can cycle through the collection of Connection objects within each packge. Check the DTS object Model in BOL and on the MS website.
The Connection objects have a property identifying whether they use SQL Server authentication or a trusted connection (SSPI). If they use SQL Server authentication, you should be able to retrieve a Username property.
The only thing this wouldn't catch is any Connection objects which are created or modified through VBScript code in an ActiveX task. We do this as a matter of practice, to make moving packages to other servers easier.
HTH.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply