[MSSQL] restricting logins to SQL Server, or limiting the number of sessions for a specific login

For example, in the following code, the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '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 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  

END;  

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017

[MSSQL] Kill Connnection to Database

=======Kill All Active Connections To A Database===
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   
--do you stuff here
   
ALTER DATABASE YourDatabase SET MULTI_USER


------------------or-------------------------
/*For MS SQL Server 2012 and above*/

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

/*For MS SQL Server 2000, 2005, 2008*/

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

------------------------------------------------
/* For a specific database */

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
       AND database_id = DB_ID('database name');

/* For all databases */

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

=> Copy kêt quả lệnh Select và  Run