SQL Server Magazine

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:

1. Determine who is logged in and what database that person is using.
2. Run KickUserOut.sql.

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:

• The spid column. Each internal process in SQL Server is assigned a server process ID (SPID). The spid column contains this identifier.
• The sid column. The sid column contains the security ID (SID) of the logged-in user who initiated the process.
• The dbid column. The dbid column contains the ID of the database being accessed by the logged-in user.

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:

• SPID, which will contain the SPID for each process
• [Status], which will contain the status (e.g., Runnable, Sleeping, Background, Rollback) of each process
• LoginName, which will contain the login name of the user who started each process
• HostName, which will contain the name of the workstation from which each process was initiated (e.g., client machine's name)
• BlockedBy, which will contain information about any blocking activity for the server's threads and processes
• DBName, which will contain the name of the database each user is accessing
• Command, which will contain information about the SQL statement that executes sp_who2
• CPUTime, which will contain information about the server's CPU
• DiskIO, which will contain information about the server's disk I/O
• LastBatchRunTime, which will contain the date and time of each user's login
• ProgramName, which will contain the name of the program that started each process (note that the program name might not be available because the program name depends on the user application)
• SPID2, which will contain the same information as the SPID column
• RequestID, which will contain the identifier of the request running within SQL Server (see the dynamic management view sys.dm_exec_requests for additional information about this identifier)

   Prev. page   [1] 2     next page