I have an application that has hundreds of users. Every now and again we need all users out of the application to run apply a fix on the data in DB (edit tables, causing triggers to be disabled during fix, etc). We don't want to set the DB to SINGLE_USER
mode, which would kill the connections and ROLL BACK TRANSACTIONS
, since often the work is being done by a third part remotely. So, we wrote the script below to remove Domain users and then set it to RESTRICTED_USER
. I'm not overly concerned with performance since the query shouldn't take long, but am concerned with unanticipated results, underlying flaws that I'm not aware of, and what would be best practice in this case.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
declare @domain varchar(64) = 'myDomain'
--used to exclude domain logins running on the server, like an agent account, etc...
declare @server varchar(64) = 'theServer'
--create temp to store sp_who2 results
if object_id('tempdb..#Who2') is not null drop table #Who2
create table #Who2 (
SPID int,
Stat varchar(4000),
LoginUser varchar(256),
HostName varchar(256),
BlkBy varchar(256),
DBName varchar(256),
Command varchar(4000),
CPUTime bigint,
DiskIO bigint,
LastBatch varchar(64),
ProgramName varchar(256),
SPID2 int,
RequestID int
)
--load sp_who2 results
insert into #Who2 exec sp_who2
--delete all users who aren't domain accounts
--remove your SPID from the list to be deleted
--remove anything running locally on the box
delete from #Who2
where
LoginUser not like + @domain + '%'
or HostName = @server
or SPID = @@SPID
--return the logins that will be dropped
select LoginUser as LoginsToBeDropped from #Who2
--cursor for the SPIDs
declare cur cursor fast_forward for
select
SPID
from #Who2
declare @Spid varchar(16)
declare @sql varchar(4000)
--loop through the SPIDs and kill them
open cur
fetch next from cur into @Spid
while @@FETCH_STATUS = 0
begin
--select @Spid
set @sql = 'kill ' + @Spid
exec(@sql)
fetch next from cur into @Spid
end
close cur
deallocate cur
go
--see what connections remain
exec sp_who2