First, I don't see that code as turning on any setting. It sets a local variable value, that's it. You happened to name that local variable "[@]IMPLICIT_TRANSACTIONS", but that doesn't mean it has anything to do with a connection setting by the same name. Similarly, setting a local variable named @XACT_ABORT to "ON" wouldn't change your XACT_ABORT setting either.

Keep in mind, too, that that implicit transaction setting only applies to dblib connections. For other connections, you'll stay in implicit transaction mode until you issue a "BEGIN TRANSACTION" statement.

There are only two settings: implicit and explicit. Autocommit applies as a consequence of being in implicit mode.