Send your SQL Server questions and comments to SQL Server MVP Brian Moran at firstname.lastname@example.org.
I need to provide an administrative interface in my application that issues the sp_droplogin command to remove a SQL Server-authenticated login from the list of valid logins. However, sp_droplogin fails if the login is connected to SQL Server. How can I determine whether the login is connected?
Although this question seems simple, I learned a couple of interesting tidbits while I was researching the answer. The simple answer is to look in the master database's sysprocesses table, which contains one row for each server process ID (SPID) open within the server. You'll quickly see which logins have an active server connection. However, you can also answer the question through some basic detective work.
You could rephrase this question as "How does sp_droplogin know that a login is currently connected?" The best way to figure out how a stored procedure does something is to look at the code. If you read through the text for sp_droplogin (which you can find in the master database), you'll turn up the nugget that Listing 1 shows. Sp_droplogin interrogates the master..sysprocesses table to determine whether a login is connected to the server—and you can do the same thing manually.
Reading the sp_droplogin procedure code gave me this second tidbit: The dormant value for the status column in sysprocesses tells you whether the SPID was established by a remote procedure call (RPC) from another server or from a connection that a linked server made. I noticed the value dormant referenced as a possible option for the status column in the master..sysprocesses table. I didn't know what this value represented, and a quick search of SQL Server Books Online (BOL) didn't turn up an answer. However, the comment "ignore cached remote connections" for the query that Listing 1 shows gave me a hint. After putting together a simple test that used a linked server, I could see on the remote linked server a SPID that had a value of dormant. I inspected the value of the status column for the SPID in question immediately after the linked-server query ran. Linked-server queries establish a connection to the remote server and keep that connection open or cached for a period of time after the linked query is finished executing. Linked-server queries behave this way because establishing the connection is relatively expensive—so SQL Server caches the connection for a period of time in case the user decides to run another query.
Reading procedure code is easier than it seems and is often a quick way to find an answer. And answering this question further proved to me that reading procedure code is a great way to learn how SQL Server works.