dts owner and errors

[hawkes1967]

I created a dts job which is run through a vb app. The job runs fine when I run it from my machine but fails when a different user using their machine trys to run the job. I have other vb apps running dts jobs so the code I'm using is the same however I noticed the dts job, created using SQL2000, that is failing for users has my name for the owner while all the other dts jobs have a blank owner. I was able to get around the problem by creating an identical dts job using SQL 7.0 enterprise manager which saved the job with a blank owner. Any ideas on what I can do in the future when I create DTS jobs using SQL2000
Thanks.

[rmiao]

Do you access remote file in dts? If so, ensure sqlagent service account has permission on remote file.

[hawkes1967]

That's a good point and I'll make sure but the two dts jobs are identical. They both reference the same remote data file and the user can run the dts package with no owner name but cannot run the dts package with the owner name specified.

[rmiao]

Are they on same server? Does owner a member of sysadmin? If not, you should configure sql agent proxy account for them.

[hawkes1967]

Yes, both jobs, they're identical in everything but name, are on the same server. The job that has an owner attached to it and is causing me the problems is owned by my user name. However, my user name has all server roles, i.e. System Administrator, Security Administrator, etc.

[andi_g69]

Faced a similar problem. Found a knowledge base article which does not match my symptoms at 100%. Anyway the first workaround did solve the issue.
Look at: KB241643

[hawkes1967]

Read the arcticle but how did you change the owner? I tried EXEC sp_changeobjectowner... but received 'does not exist' error.
I'm still stumped why all the jobs I created earlier on sql7 did not have a name in the owner field but any new jobs I've created using SQL2000 have my name attached and can only be run by me.

[rmiao]

Can't use sp_changeobjectowner to change job owner since job is not db object. You can change it in em -> management -> jobs -> properties of the job.

[hawkes1967]

My mistake I used the word job to refer to a dts package. The problems I'm experiencing are all related to two packages I've created and tried to run through a vb app. They are identical packages except the one that works has a blank owner and the one that doesn't has my user name as owner.

[rmiao]

You can try change owner by updating system table.

[andi_g69]

Or you connect to the Server where the package resides using a SQL Login. Then open the DTS package and save it under a different name. It should have the SQL login you are connected as the owner now.

[hawkes1967]

I connected to the sqlserver using a SQL login, saved the package under a different name and the package is still saved with my nt name as the owner. Seems to disregard how I am connected in enterprise manager. I am unable to change the package owner.

[JBane]

Check out the stored procedure sp_reassign_dtspackageowner in MSDB. It's fairly self explanatory and I've used it a bunch to change package owner names.

Jeff

[hawkes1967]

I used the sp_reassign_dtspackageowner to save it with the owner ''. Still not able to run it from the user's machine. I had earlier tried saving the package from the Server as the local administrator, then domain administrator, then sa and none have worked. The only way I've had the dts package run properly from the users machine is using the package created on my old SQL 7.0 machine which for some reason saves dts packages with a blank owner name. The dts packages are all exactly the same except for the owner listed. This is very frustrating. I don't want to always have to use my old machine to create dts packages.

[JBane]

I'm not sure what your resolution is. We don't have any 7.0 boxes around anymore, but I know I've been managing package owners since 7.0 came out. I'm curious as to how you are getting a blank owner in the first place. What user are you connecting to EM with on your 7.0 machine?

Jeff