Although KickUserOut.sql uses information from only the HostName, LoginName,
and DBName columns, it stores all the columns returned by sp_who2 in #utbSPWHO2.
Because of a technical limitation in SQL Server, you can't store only some of
this stored procedure's results. It's all or nothing.
In callout C, KickUserOut.sql determines the version of SQL Server installed
on the server because sp_who2 is version sensitive. SQL Server 2005 and
later returns the RequestID value, but SQL Server 2000 doesn't. Depending on
which version of SQL Server is installed, the script executes sp_who2 and inserts
the appropriate results into the temporary table #utbSPWHO2.
Now that the data it needs is in the table, KickUserOut.sql checks to see whether
the user specified in @LoginName is currently accessing the database specified
in @DatabaseName. If the user isn't currently accessing the database, the script
relays that message and quits. If the user is currently accessing the database,
the script accesses the #utbSPWHO2 table and retrieves the SPID of the first
process being accessed by the user, as callout D shows. The script also checks
the value in the HostName column to make sure that the process isn't an internal
machine process.
In the code at callout E, the script uses the KILL command to terminate the
process and all its subthreads. (You can also use the KILL command to terminate
units of work that are created by distributed transaction coordinators—DTCs.)
If the kill command terminates a process while a transaction is in progress,
the entire transaction is rolled back. Because rolling back transactions can
take a long time and consume many computational resources (especially in the
context of distributed transactions), the kill command should be used with care.
Note that sp_who2 doesn't specify the number of open transactions for a process.
However, sysprocesses does. If a process is in the middle of a transaction,
the value in the open_tran column that corresponds to that process will be set
to 1.
After the kill attempt, the script executes sp_who2 again to determine whether
the process has indeed ended, as callout F shows. If the process is still alive,
the script waits for the duration specified in the @TimeToCheckKillInSec variable
and rechecks the status of the process. This check continues until the process
is successfully terminated or the script reaches the maximum loop number specified
in the @NumberOfTimesToLoop variable. If the script stops because the loop threshold
is reached, the script informs the user that it couldn't kill the process and
quits.
If the process was successfully terminated, KickUserOut.sql returns
a message that notes the process was successfully killed. As callout G shows,
the script gets the SPID of next process it needs to terminate and the entire
BEGIN…END loop that starts in callout E and ends after callout G executes
again. As long as there's another process to terminate, this loop continues
to execute.
An Adaptable tool
KickUserOut.sql lets you easily and quickly kick users out of a database. You
can easily adapt KickUserOut.sql to perform other tasks, such as terminating
connections and activity from host machines. DBAs working in performance-sensitive
environments might even want to adapt the script so that it tracks users' activities
and measure those activities' impact on server performance. The script could
then notify them when users' activities are interfering too much with server
performance so that they can kick them off of the server.