6

Consider the following T-SQL in which the user doesn't have permission to execute SP_TRACE_GENERATEEVENT:

SET XACT_ABORT OFF;

SET ANSI_WARNINGS ON;

BEGIN TRANSACTION;

BEGIN TRY
    EXEC SP_TRACE_GENERATEEVENT;
END TRY
BEGIN CATCH
END CATCH;

COMMIT TRANSACTION;

I don't expect to get an error per the documentation for SET XACT_ABORT:

When ANSI_WARNINGS=OFF, permissions violations cause transactions to abort.

However, I receive the following error as shown in the dbfiddle:

Msg 3930 Level 16 State 1 Line 11 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3998 Level 16 State 1 Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

This issue can happen with other types of security issues besides calling SP_TRACE_GENERATEEVENT. For example, I've seen it when trying to query a table in which the user cannot connect to that database. I don't have a complete list of error cases.

Why does a permission error doom the transaction with XACT_ABORT set to OFF and ANSI_WARNINGS set to ON?

1 Answer 1

3

SP_TRACE_GENERATEEVENT is an Extended Stored Procedure

SELECT so.name, so.type_desc
FROM sys.system_objects AS so
WHERE so.name = N'SP_TRACE_GENERATEEVENT'

screenshot of query results in SSMS showing SP_TRACE_GENERATEEVENT is an extended stored procedure

Those work in a different way as per the documentation

The process by which an extended stored procedure works is:

When a client executes an extended stored procedure, the request is transmitted in tabular data stream (TDS) or Simple Object Access Protocol (SOAP) format from the client application to Microsoft SQL Server.

SQL Server searches for the DLL associated with the extended stored procedure, and loads the DLL if it is not already loaded.

SQL Server calls the requested extended stored procedure (implemented as a function inside the DLL).

The extended stored procedure passes result sets and return parameters back to the server by through the Extended Stored Procedure API.

Here's an Erland Sommarskog article about error handling in Extended Stored Procedures

SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist. Here is a brief exposé.

The most popular XP is certainly xp_cmdshell. If an error occurs when running the operating-system command, this does not raise an error in T‑SQL, but you can check the return code from xp_cmdshell or trap the output in a one-column table with INSERT-EXEC.

And this related SO question: SQL Server catch error from extended stored procedure

4
  • I get the same error when selecting from a table.
    – Joe Obbish
    Jul 18, 2022 at 20:14
  • Can you repro? When I try with missing table permissions, I don't get a doomed transaction.
    – Zikato
    Jul 18, 2022 at 20:31
  • 1
    This is the error that causes the doomed transaction: Msg 916, Level 14, State 2, Line 50 The server principal "test_perms" is not able to access the database "GFM_Training" under the current security context.
    – Joe Obbish
    Jul 18, 2022 at 20:51
  • That sounds like a Cross Database access. Hard to say without a repro script.
    – Zikato
    Jul 18, 2022 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.