October 17, 2005 at 9:54 am
Hi guys,
All my DTS packages used a login with sa rights. I dropped the login with sa rights and am running all DTS packages under a different logins with sa rights. the packages are run as scheduled jobs.
However the job fails with error - unable to login as the old sa login. why is it still trying to access the old login. i changed all the connection properties in the dts packages and also the login under which the scheduled job runs.
i changed the owner sid in sysdtspackages to match the one with sid in sysjobs.
any ideas?
thanks in advance.
October 17, 2005 at 10:37 am
Just checking that you changed the owner of the scheduled job rather than 'login under which the scheduled job runs'.
To make sure, I would login to the server as the new sa account and delete and create the job again. I'm sure I remember coming across something similar and that was how I fixed it.
Even though it shows new sa as owner of the job, I think it stores the old one, too.
October 17, 2005 at 11:38 am
Sorry. I forgot to mention. If I drop and recreate the jobs, they work fine. Interestingly, if I script the job and re-create, it still takes the old login. I restarted the agent but does'nt work. Can't re-start sql server unfortunately.
Wondering if there is another way.
October 17, 2005 at 12:30 pm
Were you aware that when you change a DTS package, the prior versions are still retained ?
Under some circumstances ( that I have yet to determine), an older version of the DTS package is executed instead of the latest version.
Try deleting all of the old versions for each of the DTS packages.
SQL = Scarcely Qualifies as a Language
October 18, 2005 at 1:48 am
Further to this, it is a good idea to use the GUID (Globally Unique Identifier) that is produced when you schedule a DTS script out of Data transformation services.
This will ensure that you never run anything but the version that you wish to.
The GUID changes whenever you save the script, so you need to place the New GUID into the job step in the scheduler when this occurs.
October 18, 2005 at 6:57 am
Phil's idea works perfect. Replacing the DTS GUID works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply