Kill All Connections For Domain Account Users

Question

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

Answer 1

When you want users out of the database, do you have a timeframe as to when they should be allowed back in? The script you have above, will get rid of the users, but those users can just create a new connection to the database. What will you do when the user reconnects? Do you need to keep them out of the database for the entire time of the maintenance? What is the goal with keeping the users out of the database during the maintenance?

Additionally, if you kick people off out of the database, are you ok with losing the transaction that may be in flight?

What kind of maintenance do you want to do the database? Is it being done during a time of day when users should not be using the database?