Data Factory Stored Procedure Error Cannot find the object

Debbie Edwards 521 Reputation points
2020-10-08T09:41:21.453+00:00

I have a stored procedure to truncate tables and it works absolutely fine in SQL Server EXEC [staging].[USP_TruncateTables] @tableName = 'example', @SchematableName = 'staging.example'; So I can use this truncate Script for all the staging tables However I set it up in Data Factory (And I have tried both ways. I have added it in the Copy Script ![30877-precopyscript.jpg][1] And I also tried by removing the above and adding a Stored Procedure Activity ![30846-spactivity.jpg][2] But both produce the same error message "errorCode": "2402", "message": "Execution fail against sql server. Sql error number: 1088. Error Message: Cannot find the object \"example\" because it does not exist or you do not have permissions.", "failureType": "UserError", "target": "Stored procedure1", "details": [] I dont think there is any issues with the User and Role because I have all the EXEC SELECT UPDATE etc permissions required. I cannot currently get past this issue. If anyone has any ideas that would be great [1]: /api/attachments/30877-precopyscript.jpg?platform=QnA [2]: /api/attachments/30846-spactivity.jpg?platform=QnA

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,043 questions
0 comments No comments
{count} vote

Accepted answer
  1. Debbie Edwards 521 Reputation points
    2020-10-08T11:08:54+00:00

    Sorted. It needed GRANT ALTER permissions


0 additional answers

Sort by: Most helpful