Kill User Connections to Database

While cleaning up some databases today a couple of them were giving me exclusive access errors during a take offline command.

After threatening the users with anger and fury I decided to believe their cries of “i dont know, i swear, d-d-d-d-d-d-ont hurt me”

Solution:
1. Create the usp_KillUsers sp from below
2. execute usp_KillUsers ‘DBName’ while in the master database
3. All locks are gone, you may now pillage and plunder

CREATE PROCEDURE usp_KillUsers
@p_DBName SYSNAME = NULL
AS
/* Check Paramaters */
/* Check for a DB name */
IF (@p_DBName IS NULL)
BEGIN
PRINT ‘You must supply a DB Name’
RETURN
END — DB is NULL
IF (@p_DBName = ‘master’)
BEGIN
PRINT ‘You cannot run this process against the master database!’
RETURN
END — Master supplied
IF (@p_DBName = DB_NAME())
BEGIN
PRINT ‘You cannot run this process against your connections database!’
RETURN
END — your database supplied
SET NOCOUNT ON
/* Declare Variables */
DECLARE @v_spid INT,
@v_SQL NVARCHAR(255)
/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @p_DBName
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @v_SQL = ‘KILL ‘ + CONVERT(NVARCHAR, @v_spid)
— PRINT @v_SQL
EXEC (@v_SQL)
END — -2
FETCH NEXT FROM c_Users INTO @v_spid
END — While
CLOSE c_Users
DEALLOCATE c_Users

Leave a Reply

Your email address will not be published. Required fields are marked *