I have created a DTS package that runs a query from my SQL Server database and stores the result as a comma/quote-delimited file on another server. It works perfectly when I execute it (I am logged on as System Administrator). However, when I schedule the package to run, it fails and gives me an error message about permissions. I can't understand why it works when I execute it but not when I schedule it. It doesn't seem to matter whether I create the package on the server, log on to the server as administrator or whether I create it on my own PC logging on with my personal login as local administrator.
DTS packages run under the context of the user that invoked them, when they are executed from one of the online methods (DTS Designer, DTS Run, etc.). However, DTS packages run under the security context of the SQL Server Agent when they are executed on a schedule. Make sure you have created a Windows user account for the SQL Server Agent that includes SA or systems admin privileges.
Do you have comments on this Ask the Expert Q&A? Let us know.
Dig Deeper on SQL Server Security
Monitoring and Administration expert Kevin Kline discusses the obstacles of database administration when a publisher resides on a different domain.
A common SQL Mail error is addressed by monitoring and administration expert Kevin Kline.
Monitoring and Administration expert Kevin Kline discusses the installation of SQL Server client tools on Windows XP Pro.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.