/*
USE msdb;
GO
REVOKE EXECUTE ON _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 
   TO USR_REFLEX_ADMIN;
GO
DROP USER USR_REFLEX_ADMIN;
GO
DROP PROCEDURE _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION;
GO
DROP SCHEMA _HARDIS_KILL;
GO
USE master;
GO
DROP LOGIN CNX_REFLEX_ADMIN ;
GO
*/




--> créer proc pour voir les sessions à tuer
USE msdb;
GO

CREATE SCHEMA _HARDIS_KILL;
GO

CREATE OR ALTER PROCEDURE _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 
   @ACTION       CHAR(4), 
   @SESSION_ID   SMALLINT
WITH EXECUTE AS OWNER, ENCRYPTION
AS
/******************************************************************************
* PROCEDURE msdb._HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION            2020-05-13 *
* VERSION 1.0                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
*******************************************************************************
* Liste les sessions HARDIS REFLEX ou tue une session HARDIS REFLEX           *
*******************************************************************************
* PARAMÈTRES EN ENTRÉE :                                                      *
*   @ACTION CHAR(4)                                                           *
*       Nature de l'action à entreprendre. Ne peut être que LIST ou KILL.     *
*   @SESSION_ID                                                               *
*       N° de la session à tuer                                               *
* PARAMÈTRE DE SORTIE                                                         *
*    (aucun)                                                                  *
* RETOUR :                                                                    *
*    si paramètre @ACTION = 'LIST', table des sessions HARDIS reflex          * 
*    si paramètre @ACTION = 'KILL', message d'exécution ou d'erreur           *
*******************************************************************************
* EXEMPLES D'UTILISATION :                                                    *
*    USE msdb;                                                                *
*    EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'LIST', NULL;                *
*       fournit la liste des sessions HARDIS Refelx avec toutes               *
*       les informations nécessaires                                          *
*    EXEC _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 'KILL', 168;                 *
*       tue la session considérée si elle appartient aux sessions HARDIS      *
*       Reflex                                                                *
*******************************************************************************
* NOTA : En cas de KILL, la procédure vérifie que la session existe toujours  *
*        et qu'elle appartient bien à un processus HARDIS reflex.             *
*******************************************************************************
*   ©   Tous droits réservés : frédéric Brouard / SQL SPOT - 2020-05-13   ©   *
******************************************************************************/
IF @ACTION NOT IN ('LIST', 'KILL') 
BEGIN
   RAISERROR('Unknown value for "action" parameter (@ACTION = %s). must be "LIST" or "KILL"', 16 , 1, @ACTION);
   RETURN;
END;

-- renvoie la liste des session HARDIS
IF @ACTION = 'LIST'
BEGIN
   SELECT c.*, s.*,
          DB_NAME(s.database_id) AS CONTEXT_DATABASE,
          lq.text AS LAST_QUERY_EXECUTED, q.text AS QUERY_RUNING,
          r.*
   FROM   sys.dm_exec_connections AS c
          JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
          LEFT OUTER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
          OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS q
          OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS lq
   WHERE  c.session_id <> @@SPID
     AND  s.login_name = 'reflex'
     AND  DB_NAME(s.database_id) LIKE 'HARDIS?_%' ESCAPE '?'
   ORDER  BY blocking_session_id DESC, COALESCE(r.session_id, 66666)
   OPTION (;
END;

-- demande un KILL
IF @ACTION = 'KILL'
BEGIN
   --> vérifier que la session existe bien
   IF NOT EXISTS(SELECT * 
                 FROM   sys.dm_exec_sessions 
                 WHERE  session_id = @SESSION_ID)
   BEGIN
      RAISERROR('Session id %d is not alive. You cannot kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   -- vérifier que la session à tuer a bien une connexion "reflex"
   IF EXISTS(SELECT * 
             FROM   sys.dm_exec_sessions 
             WHERE  session_id = @SESSION_ID 
               AND  login_name <> 'reflex')
   BEGIN
      RAISERROR('Session id %d is logged under another login account than reflex. You are not authorized to kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   -- vérifier que la session à tuer porte bien sur une base HARDIS
   IF EXISTS(SELECT * 
             FROM   sys.dm_exec_sessions 
             WHERE  session_id = @SESSION_ID 
               AND  login_name = 'reflex' 
               AND  DB_NAME(database_id) NOT LIKE 'HARDIS?_%' ESCAPE '?')
   BEGIN
      RAISERROR('Session id %d is executing queries under a non HARDIS database. You are not authorized to kill this session.', 
                16 , 1, @SESSION_ID);
      RETURN;
   END;
   --> tout va bien, on tue la session
   DECLARE @KILL_COMMAND VARCHAR(32) = 'KILL ' + CAST(@SESSION_ID AS VARCHAR(6)) + ';'
   BEGIN TRY
      EXEC (@KILL_COMMAND);
   END TRY
   BEGIN CATCH
      DECLARE @ERROR_MSG NVARCHAR(1048)
      SET @ERROR_MSG = 'HARDIS_KILL (ERREUR) : ' + ERROR_MESSAGE();
      RAISERROR(@ERROR_MSG, 16, 1) WITH LOG;
   END CATCH
   --> on ajoute cet information au journal d'événement
   DECLARE @LOGIN sysname = SYSTEM_USER
   RAISERROR('HARDIS_KILL (EXECUTE) : Session %d tué via procédure sp__HARDIS_DIAGNOSE_SESSION par le compte de connexion %s.', 
             10, 1, @SESSION_ID, @LOGIN) WITH LOG;
END
GO

USE master;
GO

CREATE LOGIN CNX_REFLEX_ADMIN 
   WITH PASSWORD = N'H@rd1s-Reflex', 
        DEFAULT_DATABASE=[master], 
        CHECK_EXPIRATION=OFF, 
        CHECK_POLICY=OFF;
GO

USE msdb
GO

CREATE USER USR_REFLEX_ADMIN 
   FOR LOGIN CNX_REFLEX_ADMIN;
GO

GRANT EXECUTE ON _HARDIS_KILL.P_HARDIS_DIAGNOSE_SESSION 
   TO USR_REFLEX_ADMIN
GO