Forced Logoffs 1

[autocutter]

Does anyone have an SP that will force users off the SQL Server? While the app can have a forced logoff, we use Win 98 machines that are famous for hanging MS applications and during shutdowns were the individual doesn't not close the app prior-both situations leaving the user in the SQL Server thus preventing intergery checks etc etc..Please help!

 

[TitleistDBA]

If you use sp_who or sp_who2 it will show you who is logged into what database it will also give you the spid. Once you know what connections you don't want use the Kill command followed by the spid.

Kill 51

http://www.acushnetcompany.com

 

[autocutter]

After looking around, I found a snippet in this forum. I only flushed out the declares, the bulk of the code is from the person who posted the code. Thanks everyone for your help!


USE Master
go

DECLARE
@nKillProcess as varchar(50),
@nFetchStatus as varchar(50),
@sTemp as varchar(50)

DECLARE curProcesses CURSOR
LOCAL
FAST_FORWARD
READ_ONLY

FOR
SELECT spid
FROM
Master..sysprocesses
WHERE
dbid = 5 -- 5 = Vet, 6 = SAA

OPEN curProcesses

FETCH NEXT FROM curProcesses INTO --Gets the first process
@nKillProcess
SET @nFetchStatus = @@FETCH_STATUS

--Kill the processes
WHILE @nFetchStatus = 0
BEGIN
SET @sTemp ='KILL ' + CAST(@nKillProcess as varchar(5))
EXEC(@sTemp)
FETCH NEXT FROM curProcesses INTO --Gets the next process
@nKillProcess
SET @nFetchStatus = @@FETCH_STATUS
END
CLOSE curProcesses
DEALLOCATE curProcesses