4

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Eva Donaldson
  • 385
  • 4
  • 18
  • 1
    Try it with the schema. – Jacob H Feb 01 '19 at 17:00
  • Unfortunately, that gives the same error. Tried with just dbo.TABLE_NAME. Tried with DBNAME.dbo.TABLE_NAME. All of that works when executed directly on the server as the user. Does not work via SSIS. – Eva Donaldson Feb 01 '19 at 18:55
  • 1
    Assuming you are executing this on a SQL Server, what kind of authentication is your connection manager using? How are you running the package (in visual studio, via SQL Agent, etc.)? It really sounds like the package is running under different credentials than you are using to manually connect and execute the statements. – Jeremy J. Feb 01 '19 at 19:00
  • 1
    What role or roles is the user account a member of? Also, can you show us how you've configured your Execute SQL Task? – digital.aaron Feb 01 '19 at 21:53
  • `TRUNCATE` operation requires more permission than `DELETE` operations, check my answer for more details. – Hadi Feb 02 '19 at 12:35

1 Answers1

2

You can split the error into two parts:

  1. Table does not exist
  2. Do not have permission

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.

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 2
    I am giving credit to this answer but I figured out what my issue was. Everytime I tested the truncate I was switching users from my admin user to this RO user. Only, I wasn't closing the query window in Management Studio. This meant wasn't ACTUALLY changing users. Hence my test was all wrong and why I was so sure I had granted the right privileges to the right user. Reminder to self: when changing users, close ALL the windows and start again. *sigh* – Eva Donaldson Feb 02 '19 at 14:55