HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Why do permission errors doom a transaction with XACT_ABORT set to OFF and ANSI_WARNINGS set to ON?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whydoomwithpermissionoffansi_warningstransactionanderrorsset

Problem

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?

Solution

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'


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

Code Snippets

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

Context

StackExchange Database Administrators Q#314551, answer score: 3

Revisions (0)

No revisions yet.