SQL SERVER 2008 R2: Limitar conexiones de inicios de sesión con Triggers DDL.

Hay veces que es interesante impedir que un Inicio de Sesión  establezca más de una conexión con el servidor. Para ello SQL Server 2008 R2 incorporar los llamados Desencadenadores (triggers) DDL. Este artículo nos servirá para poner un ejemplo de estos interesantes triggers, que a diferencia de los que usamos normalmente, que se disparan al realizar modificaciones, inserciones o borrados de datos de una tabla, estos se disparan ante alteraciones en la estructura de la base de datos, por ejemplo, o cualquier otro evento sobre el servidor.

Según la MSDN de microsoft, un Desencadendor DDL es: "Los desencadenadores DDL se inician en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a las instrucciones de Transact-SQL que comienzan por las palabras clave CREATE, ALTER, DROP, GRANT, DENY, REVOKE o UPDATE STATISTICS. Algunos procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL también pueden activar desencadenadores DDL". Podéis encontrar mas información aquí.

Centrandonos en el ejemplo:  Vamos a limitar que un inicio de sesión no pueda realizar mas de tres conexiones simultaneas al Servidor de SQL Server 2008 R2. Para ello vamos a programar un trigger DDL sobre el Inicio de Sesión deseado y el evento de LOGON.

Lo primero que hacemos es crear un nuevo Inicio de Sesión con el Management Studio, para ello hacemos click derecho sobre la carpeta "Seguridad", luego en Nuevo/Inicio de Sesión. Lo configuramos como en la imagen.

Ahora vamos a darle todos los permisos posibles, es decir, será administrador.

Creamos por último el trigger DDL para controlar el número de conexiones:

01 USE master;
02 GO
03 CREATE TRIGGER limite3conexiones_usuarioLimitado
04 ON ALL SERVER WITH EXECUTE AS 'usuarioLimitado'
05 FOR LOGON
06 AS
07 BEGIN
08 IF ORIGINAL_LOGIN()= 'usuarioLimitado' AND
09 (SELECT COUNT(*) FROM sys.dm_exec_sessions
10 WHERE is_user_process = 1 AND
11 original_login_name = 'usuarioLimitado') > 3
12 ROLLBACK;
13 END;

Para probarlo, tan solo tenemos que realizar varias conexiones con el inicio de sesión que hemos creado, hasta superar el limite.