[Sysadmin] Kick users after midnight

Not mine, but we use it. Pretty basic - looks for SPIDs that are in a particular database and kills the SPIDs. We use it to ensure that people don't have active connections during maintenance time.

The one downside is that it's very literal - if you aren't explicitly in the system as sysadmin, out you go. Set in a job to run right before your maintenance.


set nocount on
declare @spid nvarchar(10)
declare @killem nvarchar(20)
declare spid_csr insensitive cursor for
select spid from master..sysprocesses
where sid not in
(
select sid from master..syslogins
where sysadmin = 1
)
and dbid in (db_id('Main'),db_id('AnotherOne'))
and loginame like 'MyDomain%'
open spid_csr
fetch next from spid_csr into @spid
while @@fetch_status = 0
begin
select @killem = 'kill ' + convert(varchar(3),@spid)
exec (@killem)
fetch next from spid_csr into @spid
end
close spid_csr
deallocate spid_csr