debugsqlMinor
Why do permission errors doom a transaction with XACT_ABORT set to OFF and ANSI_WARNINGS set to ON?
Viewed 0 times
whydoomwithpermissionoffansi_warningstransactionanderrorsset
Problem
Consider the following T-SQL in which the user doesn't have permission to execute
I don't expect to get an error per the documentation for
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
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
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
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.