Try this script (replace all of the "at_" with the at symbol). This will
list all of the packages and the connections in them using SLQ
authentication to a table variable and select at the end for "sa":
DECLARE at_object int
DECLARE at_pkgname nvarchar(255)
declare at_rc int
DECLARE at_src varchar(255)
Declare at_desc varchar(255)
Declare at_Numof int
Declare at_NumofItems int
declare at_i int
declare at_j int
Declare at_property varchar(8000)
Declare at_property_value varchar(8000)
Declare at_property_name varchar(8000)
Declare at_Connection_Name varchar(8000)
Declare at_TrustedConnection varchar(8000)
Declare at_LoginID varchar(8000)
Declare at_Database varchar(8000)
Declare at_ProviderID varchar(8000)
declare at_pkgs table (PackageName varchar(1000))
declare at_msgs table (
PackageName varchar(1000),
ConnectionName varchar(1000),
LoginID varchar(1000),
DatabaseName varchar(1000))
insert at_pkgs select distinct name from msdb.dbo.sysdtspackages
declare packages cursor for
select PackageName from at_pkgs
open packages
fetch next from packages into at_pkgname
while at_at_fetch_status=0
begin
--Creating object
EXEC at_rc = sp_OACreate 'DTS.Package', at_object OUTPUT
--IF at_rc <> 0 goto PrintError
-- Load Package from Source Server
EXEC at_rc = sp_OAMethod at_object, 'LoadFromSQLServer',
-- SQL Server Authentication
-- NULL,'HOMEPC','login','password','0','','','',at_pkgname
-- Windows Authentication
NULL,'HOMEPC','','','256','','','',at_pkgname
EXEC at_rc = sp_OAGetProperty at_object, 'Connections.Count', at_Numof OUT
set at_i=0
While at_i < at_Numof
begin
set at_i = at_i + 1
set at_property = 'Connections(' + rtrim(cast(at_i as char)) +
').Properties("ProviderID").Value'
EXEC at_rc = sp_OAGetProperty at_object, at_property, at_ProviderID
OUT
if at_ProviderID='SQLOLEDB'
begin
set at_property = 'Connections(' + rtrim(cast(at_i as char)) +
').Properties("UseTrustedConnection").Value'
EXEC at_rc = sp_OAGetProperty at_object, at_property,
at_TrustedConnection OUT
if at_TrustedConnection='False'
begin
set at_property = 'Connections(' + rtrim(cast(at_i as char)) +
').Properties("Name").Value'
EXEC at_rc = sp_OAGetProperty at_object, at_property,
at_Connection_Name OUT
set at_property = 'Connections(' + rtrim(cast(at_i as char)) +
').Properties("UserID").Value'
EXEC at_rc = sp_OAGetProperty at_object, at_property, at_LoginID
OUT
set at_property = 'Connections(' + rtrim(cast(at_i as char)) +
').Properties("Catalog").Value'
EXEC at_rc = sp_OAGetProperty at_object, at_property, at_Database
OUT
insert at_msgs
select at_Pkgname as Package_Name,
at_Connection_Name as Connection_Name,
at_LoginID as LoginID,
at_Database as "Database"
end
end
end
EXEC at_rc = sp_OADestroy at_object
fetch next from packages into at_pkgname
end
close packages
deallocate packages
select * from at_msgs where LoginID='sa'
Trevor Weir
-----Original Message-----
From: Uday Shivamurthy
[mailto:mssqldba-ezmlmshield-x76171105.[Email address protected]
Sent: Tuesday, April 26, 2005 7:52 AM
To: LazyDBA Discussion
Subject: RE: How will i know that which DTS packages, connection object ha s
set the sa password ?
Oh man, out of my depth already. I'm curious to understand this stuff
myself. Is there a "true" link b/w dts pkges and sysxlogins?
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]
-----Original Message-----
From: Kapadia_Ghanshyambhai
[mailto:mssqldba-ezmlmshield-x84314242.[Email address protected]
Sent: Tuesday, 26 April 2005 9:46 PM
To: LazyDBA Discussion
Subject: RE: How will i know that which DTS packages, connection object ha s
set the sa password ?
Hi Uday and Siddharth,
Thanks for your reply.
How will you link master..sysxlogins to dtspackages related tables
(msdb..sysdtspackages )?
Your help is highly appreciated
Regards,
Ghanshyam Kapadia (Sr.DBA)
EMC˛
where information lives
Phone: +91-80-2678-8930 Xtn-7001
Cell: +91-98805-93991
Email:[Email address protected]
-----Original Message-----
From: Uday Shivamurthy
[mailto:mssqldba-ezmlmshield-x1814480.[Email address protected]
Sent: Tuesday, 26 April 2005 16:50
To: LazyDBA Discussion
Subject: RE: How will i know that which DTS packages, connection object has
se t the sa password ?
Hi Ghanshyam,
I don't think there is an easy way around for this problem.
Option 1
Save every dts pkg as a "vbs" file and look for credentials in the
file and change them.
Option 2
Since you've taken the pains to open each pkg, you may as well visit
the connection object and fix up credentials in each pkg.
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]
-----Original Message-----
From: Kapadia_Ghanshyambhai
[mailto:mssqldba-ezmlmshield-x66535729.[Email address protected]
Sent: Tuesday, 26 April 2005 9:11 PM
To: LazyDBA Discussion
Subject: How will i know that which DTS packages, connection object has se t
the sa password ?
Hi MSSQL Gurus
Actually, we're having a lots of DTS packages on different server and we've
changed the sa password for the all the server.
Now, a question is: How will know that which DTS packages has used sa as
user name and password for the given connection object in the DTS packages?
If you can provide the system table name which contain the sa password for
the said DTS packages and connection object, that will help me a lot.
Thanks for your help in advance
Regards,
Ghanshyam Kapadia (Sr.DBA)
EMC˛
where information lives
Phone: +91-80-2678-8930 Xtn-7001
Cell: +91-98805-93991
Email:[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page