What is the scope of a SET XACT_ABORT statement in SQL Server 2005? i.e.:begin-end block, procedure or trigger, connection, database, server?
Technet Using Options in SQL Server hints that all SET options are scoped at connection or batch level.
MSDN SET Statements adds details:
If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.
It's also possible to enable XACT_ABORT by default for all users via user options:
EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE
It can also be enforced for selected users only via custom logon trigger.
See also important details on XACT_ABORT behaviour.