How to fix Azure Error Message: “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

There can be a number of reasons for this error but below steps through a potential scenario and fix for the error as referenced in the post title.

(TL;DR it’s probably down to permissions. Code example below.)

If you are running a Data Factory pipeline that calls a Store Procedure the process may fail throwing the error “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

The first part of that error is boilerplate stuff i.e. the query cannot do what it has to do so it is rolling back.

The second part of the error is what needs to be addressed. Either the object does not exist or the credentials the process is using does not have the required permission to do what the query has specified.

If you can confirm the object exits i.e. the table, then it can only be a matter of credential permissions.

The question becomes what are you trying to do with the object?

SELECT, DELETE, TRUNCATE all require that the credentials i.e. the user, has the permissions to carry out those statements.

If your query needs to TRUNCATE a table and the credentials used by Data Factory do not have permission to perform a TRUNCATE on a table then Azure will throw the aforementioned error.

In order to resolve that problem specifically, i.e. for a user to TRUNCATE a table, the user needs ALTER permissions. Below is an example of how to apply ALTER permissions on the object TestTable in the schema dbo to the user TestUser. Just update the schema, object and user to fit your needs.

GRANT ALTER ON OBJECT::dbo.TestTable TO TestUser;
GO 

The above conforms to applying the principle of least privilege as the ability to alter objects only applies to that object specifically. In closing if the object exists look at what the query does to object and ensure the user has the required permissions to do it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s