Specifies a login procedure that sets connection compatibility options at start-up.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY SECURITY OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
The initial connection compatibility options settings are controlled using the LOGIN_PROCEDURE option, which is called after all the checks have been performed to verify that the connection is valid. The LOGIN_PROCEDURE option names a stored procedure to run when users connect. The default setting is to use the sp_login_environment system stored procedure. You can specify a different stored procedure. The procedure specified by the LOGIN_PROCEDURE option is not executed for event connections.
The sp_login_environment procedure checks to see if the connection is being made over TDS. If the connection is made over TDS, sp_login_environment calls the sp_tsql_environment procedure, which sets several options to new default values for the current connection.