This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I had been avoiding upgrading to 11.3 since things like this always seem to pop up so this is a little late but maybe someone will still find it helpful.
I had a number of workflows writing to SQL tables via ODBC connections. After the upgrade, some still worked while others threw the following error -
Error running TRUNCATE table "TableName": [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "TableName" because it does not exist or you do not have permissions.
All of the connections were using the Delete Data and Append output option.
I knew upgrading could not have changed my permissions on the tables or have made the tables disappear but just to be sure I checked that I could read the tables in and that I still had the crucial and sometimes missing "Delete" permission on all the tables. All of that was unchanged.
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.
I checked with my DBA and although I did have DELETE permission on the table, I did not have ALTER permission. Once I got it, I could write to the table no problem.
The backend SQL on the output tool for Delete Data and Append output option must have changed between 10.5 and 11.3 and my companies policy of granting the minimum permission seems to have stopped my ability to write to these tables. The tables that I could write to were in a data lake that I had wide open access to.
I recently upgraded to 11.7 and found this issue as well and I was able to "Delete Data & Append" before the upgrade. Unfortunately, my DBA cannot grant ALTER permission due to our internal security policy. Here is our resolution and it works fine.
Use “Pre Create SQL Statement” to Delete the data and use “Append Existing” instead. It works fine. Just want to share another option.