MSSQL‎ > ‎

Show/Disconnect Users

Show Users:
USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')

Disconnect Users:
USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
datediff(mi, @start, current_timestamp) < 5)
begin
DECLARE spids CURSOR FOR
SELECT convert(varchar, spid) FROM sysprocesses
WHERE dbid = db_id(@dbname)
OPEN spids
while(1=1)
BEGIN
FETCH spids INTO @spid
IF @@fetch_status < 0 BREAK
exec('kill ' + @spid)
END
DEALLOCATE spids
END
GO

Comments