I have an SSIS package where I want to clear a staging table and repopulate it. I have created an Execute SQL Task of TRUNCATE TABLE TABLE_NAME that fails with the error:
Table does not exist or you do not have permission
If I alter the task to do a DELETE FROM TABLE_NAME it works like a charm. If I log in as the user I am connecting with and execute TRUNCATE TABLE TABLE_NAME, it also works like a charm. Can anyone help me understand why I cannot execute the truncate in the SSIS job, but I can as the user? Because, I would much prefer to truncate the table rather than do a delete.
You can split the error into two parts:
So if you are sure that the table exists (As you say that DELETE operations works well), it is a permission issue, check the database administrator for that maybe you don't have a permission to truncate data from this table.
Note that TRUNCATE TABLE requires more permissions then DELETE operations. Based on the official documentation:
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
On the other hand, the DELETE operation requires less permissions. Based on the official documentation:
DELETE permissions are required on the target table. SELECT permissions are also required if the statement contains a WHERE clause.
DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.