FIX: DTS Fails to Transfer Rows When a Non-Sysadmin User Opens Packages from T-SQL with Xp

SYMPTOMS

When a user who is not a member of the sysadmin role executes a Data Transformation Services (DTS) package by using the xp_cmdshell extended stored procedure and the Dtsrun utility, the package may fail to transfer data from the source to the destination. Even though no data transfers, the package execution may report a successful completion (such as in the following error message) and not display any errors:

Dtsrun: Loading...
Dtsrun: Executing...
Dtsrun: Package execution complete.

You may experience similar behavior when you run packages as scheduled jobs. The jobs may report success (although no data is transferred) or failure.

CAUSE

This problem occurs because:

When a non-sysadmin user executes the xp_cmdshell stored procedure and the Dtsrun utility, the command runs under the security context of the SQLAgentCmdExec account.

-or-
When a non-sysadmin user schedules a package, the non-sysadmin user uses the SQLAgentCmdExec account security context.

When SQL Server executes the DTS package, SQL Server creates and deletes the temporary files in the SQL Server TEMP folder. This is a local account, which does not have permissions for the TEMP folder, which therefore causes the behavior that is described in the "Symptoms" section of this article.

RESOLUTION

SQL Server 2000

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

SQL Server 7.0

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

WORKAROUND

To work around this problem, grant the SQL Agent proxy account read and write permissions to the SQL Server service startup account's TEMP folder.

SQL Server 2000
For Microsoft SQL Server 2000:

Give the Microsoft Windows NT account that you use for the SQL Agent proxy account read and write permissions to the SQL Server TEMP folder (Drive letter:\Documents and Settings\User1\Local Settings\Temp), where User1 is the SQL Server startup account.

-or-
Set the SQL Server TEMP folder to a folder to which the SQL Agent proxy account has read and write permissions.

SQL Server 7.0
For SQL Server 7.0, give the SQLAgentCmdExec account read and write permissions to the C:\Temp folder.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

SQL Server 2000
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

SQL Server 7.0
This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.