I've tried several different possibilities but don't have enough basic
understanding of the ownerships and permissions that are involved so
I'm looking for someone to explain what is required:
I created a DTS package that includes a drop table, create table, and
data transformation task. Originally, my owner was local to the pc
workstation with administrator priveleges. When I executed the
package on demand (manually, not scheduled), it runs just fine. No
errors. When I schedule the package to run at a scheduled day/time,
it fails with an access is denied message.
I originally was using the sql admin user and password in the
connection but the view job history kept referencing "Executed as
user: sqlservername\SYSTEM...." and errored with an access is denied
message. Where did it get the SYSTEM user from since my connection
used the sql admin user in my DTS package? Do I need to determine how
to get the sql admin user or should the SYSTEM user really have to run
this scheduled job? How do I get the sql admin user to show as the
"executed as user"?
I created another admin user to see if it had someone to do with the
sql admin account but I got similar results. I tried creating the
package as an NT domain user but got similar results.
I'm sure it has something to do with permissions and ownerships but I
really don't have a clear understanding of how it was intended to
work.
Any advise you can give is greatly appreciated. I've read alot of
postings and I'm still not clear on the requirements.
