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 installation of SQL Server client tools on Windows XP Pro.
Having trouble installing SQL Server 2000 Enterprise Edition on a machine that's running Windows XP. In this expert response, Monitoring and ...
Oracle uses its own dialect of SQL called PL/SQL, while SQL Server uses T-SQL, making it difficult to get Oracle stored procedures to work on a SQL ...