Desencadenadores de inicio de sesión

  • Artículo

Se aplica a:SQL ServerAzure SQL Managed Instance

Los desencadenadores de inicio de sesión activan procedimientos almacenados en respuesta a un evento LOGON. Este evento se genera cuando se establece una sesión de usuario con una instancia de SQL Server. Los desencadenadores LOGON se activan después de que termine la fase de autenticación del inicio de sesión, pero antes de que se establezca la sesión de usuario. Por tanto, todos los mensajes que se originan dentro del desencadenador y que normalmente llegarían hasta el usuario, como los mensajes de error y los mensajes de la instrucción PRINT, se desvían al registro de errores de SQL Server. Los desencadenadores LOGON no se activan si se produce un error en la autenticación.

Puede utilizar desencadenadores logon para realizar auditorías y controlar sesiones de servidor, como el seguimiento de la actividad de inicio de sesión, la restricción de inicios de sesión en SQL Server o la limitación del número de sesiones para un inicio de sesión específico. Por ejemplo, en el siguiente código, el desencadenador de inicio de sesión rechaza los intentos de iniciar sesión en SQL Server iniciados por el inicio de sesión login_test si ya hay tres sesiones de usuario creadas por dicho inicio de sesión.

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

El evento LOGON se corresponde con el evento de seguimiento de SQL AUDIT_LOGIN, que se puede usar en notificaciones de eventos. La diferencia principal entre los desencadenadores y las notificaciones de eventos radica en que los desencadenadores se generan de forma sincrónica con los eventos, mientras que las notificaciones de eventos son asincrónicas. Esto significa, por ejemplo, que si desea que no se establezca una sesión, debe utilizar un desencadenador logon. Para este fin, no se puede usar una notificación de eventos en un evento AUDIT_LOGIN.

Especificar el primer y el último desencadenador

Se pueden definir varios desencadenadores en el evento LOGON. Cualquiera de estos desencadenadores se puede designar como el primero o el último en activarse en un evento mediante el procedimiento almacenado del sistema sp_settriggerorder . SQL Server no garantiza el orden de ejecución del resto de desencadenadores.

Administración de transacciones

Antes de que SQL Server active un desencadenador LOGON, SQL Server crea una transacción implícita que es independiente de cualquier transacción de usuario. Por consiguiente, cuando el primer desencadenador LOGON inicia la activación, el recuento de transacciones es 1. Una vez que todos los desencadenadores LOGON finalizan su ejecución, se confirma la transacción. Al igual que ocurre con otros tipos de desencadenadores, SQL Server devuelve un error si un desencadenador LOGON termina de ejecutarse con un recuento de transacciones de 0. La instrucción ROLLBACK TRANSACTION restablece el recuento de transacciones en 0, incluso si la instrucción se ha emitido desde una transacción anidada. COMMIT TRANSACTION puede reducir el recuento de transacciones a 0. Por tanto, aconsejamos que no se emitan instrucciones COMMIT TRANSACTION desde desencadenadores de inicio de sesión.

Piense lo siguiente cuando esté utilizando una instrucción ROLLBACK TRANSACTION dentro de los desencadenadores de inicio de sesión:

  • Se revierten todas las modificaciones de datos realizadas antes de emitir la instrucción ROLLBACK TRANSACTION. Estas modificaciones incluyen los cambios realizados por el desencadenador actual y por desencadenantes anteriores que se ejecutaron en el mismo evento. No se ejecutan los desencadenadores restantes para el evento específico.

  • El desencadenador actual continúa ejecutando cualquier sentencia restante que aparezca después de la instrucción ROLLBACK. Si alguna de estas instrucciones modifica datos, no se revierten las modificaciones.

Una sesión de usuario no está establecida si se produce cualquiera de las siguientes condiciones durante la ejecución de un desencadenador en un evento LOGON:

  • La transacción implícita original se revierte o produce un error.
  • Un error con una gravedad mayor que 20 se genera dentro del cuerpo del desencadenador.

Deshabilitar un desencadenador de inicio de sesión

Un desencadenador LOGON puede evitar la conexión a Motor de base de datos de todos los usuarios, incluidos los miembros del rol fijo de servidor sysadmin . Cuando el desencadenador LOGON evita que se realicen las conexiones, los miembros del rol fijo de servidor sysadmin pueden conectarse mediante la conexión de administrador dedicada o iniciando Motor de base de datos en modo de configuración mínima (-f). Para más información, consulte Opciones de inicio del servicio de motor de base de datos.

Contenido relacionado