Scheduled package running under different user id 1

[newtechy]

I have written a package that accesses a shared windows directory and imports a text file from it to Sql Server. When I run this package manually, it works fine. However, when I schedule it to run as a job, it fails noting that the file on the shared directory is not accessible. It turns out that when I run the job manually, I'm running it under a different user id which has read/write access permission to the shared drive. However, when I run it via the job scheduler, I'm running it under a different user id which has no access to the shared drive. How can I change the job scheduler to use the correct user id - the one that I'm running the package under manually? Your help is appreciated. Thanks

 

[john76]

I had a similar problem. The way I solved it was to go to the windows services (on 2000, right click "My Computer" select "Manage", then expand "Services and Applications" and select "Services") and change the SQLSERVERAGENT service's "Log on as" property.

If the folder is on the same computer as SQL Server you should be able to change this property to the local system account, if it is on another PC or domain, your login will work (obviously) but you will likely need to change the service's password whenever you change your own.

If your password changes often (as mine does, every 90 days) you might want to look into getting a login that has the rights you need with a static password.

Hope this helps!

John

 

[john76]

Also, don't forget to restart the SQLSERVERAGENT service after you change the login information or it won't take.

John

 

[rasETL]

I may be wrong, but I think you can change the Userid in Enterprise Manager - Management - SQL Server Agent - Jobs.

You right-click on the Job/Schedule and you can change the
Owner/ID used in the General Tab.

 

[newtechy]

It worked!! THanks a million for the info

 

[john76]

In response to rasanders,

I also could be wrong, but I don't believe that changing the Owner alters the user permission set that the job executes under. I think that that is controlled by the login used by the SQLSERVERAGENT service.

I seem to remember trying your suggestion first without success, but it has been a while since I came across this issue and I don't remember for certain.

John

PS - newtechy, which advice worked for you? It would be good for future members searching the archives to know which one did the trick.

 

[newtechy]

John:

I would like to say yours did. Thanks a bunch for your help

 

[rasETL]

John

I may be wrong, but I believe my suggestion does change the Userid the Job executes under. It is, after all under - SQL Server Agent - Jobs.

But, I have been known to be wrong.

 

[john76]

I may be wrong, but I may be wrong!

Cheers all!

John

 

[arrow483]

Once I thought I was wrong, but I was mistaken

 

[john76]

arrow, you get a star from me for making me smile during a crappy day.

John