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