Kick a User Out of a Database in Just 2 Steps

Developers, quality assurance (QA) engineers, non-administrators, and other database users sometimes execute code that affects services and applications in production, not realizing the code's associated performance implications. Consequently, you must kick these users out of the database and terminate their SQL Server process activity. To do so, you can follow a simple two-step process:

Step 1: Determine the User and Database

Using sysprocesses, it's easy to find out which users are logged in to a server and check the databases they're using. This system table is available on the master database in SQL Server 2000 and contains useful information about internal SQL processes, threads, and subthreads. In SQL Server 2005, sysprocesses is available as a backward-compatible view. After you've accessed sysprocesses, look at the following columns:

Other interesting columns in sysprocesses include the uid, login_time, and command columns. The uid column holds the user ID as it appears in the sysusers object within the database (sysusers is a table in SQL Server 2000 and is available as a view in SQL Server 2005). The login_time column captures the date and time of the user's login. The command column provides information about the currently executed SQL statements. The sysprocesses table also includes various columns that contain useful performance metrics, such as the server's CPU, disk I/O, memory usage, and blocking activity.

After you have the SID and database ID, go to the command line. Run the command

SUSER_SNAME(sid)

where sid is the SID of the user you need to kick out. The SUSER_SNAME function will return that user's login name. Then, run the command

DB_NAME(dbid)

where dbid is the ID of the database the user is accessing. The DB_NAME function will return that database's name.

Step 2: Run KickUserOut.sql

KickUserOut.sql in Listing 1 demonstrates how you can use a T-SQL script to kick a user out of a database. The script starts out by declaring variables and setting their values. The variables in callout A are among the most important. The @LoginName variable specifies the login name of the user to kick out of the database that's specified in the @DatabaseName variable. In this case, the user sa is being kicked out of the master database. You need to customize these variables' values with the login and database names you obtained in Step 1.

Two other variables you might want to customize are @NumberOfTimesToLoop and @TimeToCheckKillInSec. The @NumberOfTimesToLoop variable specifies the maximum number of loops the script should make to check whether a process has been terminated. This variable can accept any positive integer value. The @TimeToCheckKillInSec variable specifies the maximum number of seconds to wait between checks. The value can range from 1 to 599 seconds.

After declaring and setting the variables, KickUserOut.sql creates a temporary table named #utbSPWHO2. This table will hold the results of the sp_who2 stored procedure. This stored procedure, which is available in SQL Server 2005 and SQL Server 2000, retrieves relevant data from sysprocesses. If you want to learn about sp_who2 and understand how it uses sysprocesses, you can run the command

EXEC sp_helptext ‘sp_who2'

against the master database. It's important to note that if a server has multiple CPUs, SQL Server can choose to invoke parallelism to execute a user statement. In the event of parallelism, multiple subthreads with the same SPID are returned in the sp_who2 results. The sp_who2 stored procedure doesn't return the unique identifier for each subthread. However, you can find the unique subthread identifier in the ecid column in sysprocesses. Alternatively, the sp_who stored procedure, which is an older version of sp_who2, returns this value. You can find sp_who in both SQL Server 2005 and SQL Server 2000.

Callout B shows the code that creates the #utbSPWHO2 table and its clustered index. The table contains the following columns:

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.