The state of the MSSQL Options set during query after it ends
If you execute an explicit ad-hoc SET XACT_ABORT ON, the setting remains in effect for the duration of the current session. Other session settings will remain unchanged. 

In the case of SET XACT_ABORT within a module (e.g. stored procedure or trigger), the setting is effective only within the scope of module execution and reverts to the original session setting afterwards.

If the application doesn't explicitly execute SET XACT_ABORT ON or SET XACT_ABORT OFF, the server user options configuration value may specify SET XACT_ABORT ON as the server-wide default by turning on bit 16384. The default configuration value is 0.

The SET Statements documentation describes general behavior of SET statements. Other settings (not including XACT_ABORT) can be turned ON (but not OFF) at the database level to determine default session settings for that session database context.