I must admit I'm surprised that having SET XACT_ABORT OFF is the default. I take the point that READ COMMITTED breaks the rules of isolation anyway, but I find this one even more alarming. This from the Transactions topic in Books Online:

It is the responsibility of an enterprise database system, such as an instance of the Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. The Database Engine provides:

- Locking facilities that preserve transaction isolation.

- Logging facilities that ensure transaction durability. Even if the server hardware, operating system, or the instance of the Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure.

- Transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed, or the instance of the Database Engine undoes all of the data modifications made since the transaction started.

What it should really point out is that the facilities described in the first and third points aren't turned on by default.

John