SQL Server 2008 job fails when deleting records in Linked Server
Posted on 2014-03-10
I have a SQL Server 2008 SSIS package that deletes records from a table in a Linked Server. The linked server (myServerB) is SQL Server 2000.
When I run the package from Integration Services it completes with no errors and deletes the records from the table on the linked server. But when I create a new job from SQL Server Agent and then click on "Start Job at Step..." it fails with the following error:
Executed as user: DOMAIN\ServerName$. Code: 0xC002F210
Delete permission denied on object 'table', database 'mydb', owner 'dbo'. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1).
I know the sql task works because when I run the package from Integration Services it completes successfully and deletes the records from the table, but when I try to execute the package from jobs in SQL Server Agent it fails. I have the owner as DOMAIN\administrator, why is it executing as DOMAIN\ServerName$?
How can I get the job to run the package successfully?
Thank you in advance.