CREATE TRIGGER ON ALL SERVER FOR LOGON

USE DW;

GO

-- ===============================================================================

-- Autor:                 MANUEL OMAR OLGUÍN HERNÁNDEZ

-- Fecha:                 2020 MAYO 28

-- Versión:               1.0

-- Requerimiento:         TRIGGER LOGON

--                        A NIVEL SERVIDOR

-- Descripcion:           NO PERMITE MÁS DE 10 CONEXIONES AL MISMO TIEMPO

-- =============================================================================

DROP TRIGGER connection_limit_trigger  ON  ALL SERVER

go

CREATE TRIGGER connection_limit_trigger 

ON ALL SERVER

FOR LOGON 

AS 

BEGIN 

      

       DECLARE @IdSession  SMALLINT;

       DECLARE @strSQL            NVARCHAR (50);

      

      

       IF  (

             SELECT COUNT(*)

             FROM   sys.dm_exec_sessions 

             WHERE  is_user_process = 1

             AND          login_name = ORIGINAL_LOGIN()

             ) > 10

             BEGIN

                    SET @IdSession = ( 

                                       SELECT TOP 1 Session_id

                                       FROM sys.dm_exec_sessions 

                                       WHERE is_user_process = 1

                                       AND login_name = ORIGINAL_LOGIN()

                                       ORDER BY login_time DESC

                                  );

                    SET @strSQL = N'KILL ' + CONVERT (VARCHAR(3), @IdSession)

                    EXEC (@strSQL)

             END

END;