I have an Azure SQL server and my ADF has contributor access to it. But still when I tried to truncate the table using the pre-copy script in the copy activity it throwed me error stating that you might not have access.

I am trying to copy an excel file from data lake to Azure SQL and I can create a table using the ‘Auto create table’ option in the copy activity. But, after the first copy, when I try to use the ‘use existing table’ option and give pre-copy script as ‘truncate table [schema].[tablename]’ it throws error. I tried using ‘script’ activity for the same and it throwed the same error. It is quite weird that I can create a table and load data using ADF but cannot Truncate the table.

Could someone help me understand why this is happening? Am I missing something?