SQL Puzzle | How to Kill all other existing SQL server connection except the login started by current session

Write a script to Kill all other existing SQL server connection except the login started by current session

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

DECLARE @Id AS VARCHAR(MAX)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills

SELECT CAST(SPID as VARCHAR(10)) Id INTO #TempBills
FROM MASTER.dbo.sysprocesses sp 
CROSS APPLY
(
	SELECT * FROM 	
	MASTER.dbo.sysdatabases sd WHERE sp.dbid = sd.dbid
        AND nt_username <> 'SQLSERVERAGENT' AND  nt_username  <> ''
)l

WHILE EXISTS (SELECT TOP 1 Id FROM #TempBills)
BEGIN

	 SELECT TOP 1 @Id = Id FROM #TempBills

	 EXEC( 'KILL ' + @Id )

	 DELETE FROM #TempBills WHERE Id = @Id

END

--

[ Note- Al though I don’t prefer this command. You can use it as per your need. Also be careful when run it on the pre production and production environment. ]

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com