Kbase P142097: How to kill off all users in SQL server?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/27/2009 |
|
Status: Unverified
GOAL:
How to kill off all users in SQL server?
GOAL:
How to use SQL server TSQL script to disconnect users in the database?
FACT(s) (Environment):
Windows
MS SQL Server
FIX:
Following SQL script disconnect all users currently connected to the SQL Server database
-- * the database.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
-- Create temp table to store results of sp_who
create table #who
(
spid int,
ecid int,
status varchar(30),
loginname varchar(35),
hostname varchar(15),
blk int,
dbname varchar(30),
cmd varchar(30)
)
declare @spid int
declare @killstatement nvarchar(10)
-- Run sp_who to get the users connected to each database
insert into #who exec sp_who
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select spid from #who where dbname = @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(6))
exec sp_executesql @killstatement
-- Print killed spid
print @spid
end
fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1
drop table #who