Start the SQL Server Enterprise Manager. Expand your server, and look at Management--> Current activity --> Process Info. All current connections are listed. If you right click a connection, you can select Kill process to kill the process. Of course you need the proper privileges for this.
Well, I have never done this, but you could add a reference to Microsoft SQLDMO. The SQLServer class has an EnumProcesss and a KillProcess method. I assume these will do the job.
You got me interested, and I put something together.
Add two commandbuttons (cmdEnum and cmdKill) and a listbox (List1) to a form. Add a reference to Microsoft SQLDMO object library (I assume you have installed the SQL Server components) and add this code. You have to change the code in the form load event; set the servername, login name and password.
Code:
Option Explicit
Dim svr As SQLServer
Private Sub Form_Load()
Set svr = New SQLServer
svr.Connect "MyServerName", "sa", ""
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
svr.Close
Set svr = Nothing
End Sub
Private Sub cmdEnum_Click()
Dim qr As QueryResults
Dim iCnt As Integer
List1.Clear
Set qr = svr.EnumProcesses
For iCnt = 1 To qr.Rows
List1.AddItem qr.GetColumnString(iCnt, 3)
List1.ItemData(List1.NewIndex) = qr.GetColumnLong(iCnt, 1)
Next
Set qr = Nothing
'EnumProcesses will return a resultset containing:
'spid (smallint, SQL Server process ID)
'status (nchar(31), Execution state, such as running or sleeping)
'loginname (nvarchar(129), Name of the SQL Server login)
'hostname (nchar(129), If applicable, network name of the client workstation)
'program_name (nchar(129), If applicable, name of the client application)
'cmd (nchar(34), Abbreviated indicator of current command. AWAITING COMMAND when no command is current)
'dbname (nvarchar(129), Database currently in use by process)
'cpu (integer, Cumulative CPU time for process)
'memusage (integer, Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process)
'blocked (smallint, When nonnull, process ID blocking a request of the process ID listed by the row)
End Sub
Private Sub cmdKill_Click()
If List1.ListIndex <> -1 Then
If MsgBox("Are you sure you want to kill this process?", vbYesNo) = vbYes Then
svr.KillProcess List1.ItemData(List1.ListIndex)
End If
End If
End Sub
Click the cmdEnum button to fill the listbox. Select an item in the list and click the cmdKill button. I would suggest to be carefull with testing this, since you could get some angry users.