Recently came across a situation where reporting logins were interfering with nightly jobs due to blocking. After a number of attempts of trying to resolve the blocking, it was decided that a stored procedure that disabled the login and killed the user sessions was the most pragmatic solution. This is the code I came up with to resolve the issue.

SET NOCOUNT ON 
DECLARE @rowsToProcess INT;
DECLARE @CurrentRow INT; 
DECLARE @SelectSpid INT;
DECLARE @user VARCHAR(30); 
DECLARE @killString varchar(50); 
DECLARE @DisableLogin VARCHAR(100); 

SET @user = ''; -- add user 

-- disable login of user to prevent more logins 
SET @DisableLogin = 'Alter Login ' + @user + ' Disable;'; 
EXEC(@DisableLogin); 

-- Create Table variable to store the spid info 
Declare @BusinessObjectsTable Table
 ( 
 	rowid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	spid INT, 
	loginname VARCHAR(30), 
	spid_status VARCHAR(15)
 ); 
 
-- query for the SPID based on user and store it in the table variable 
INSERT INTO @ReportObjectsTable (spid, loginname, spid_status) 
SELECT spid, loginame, status FROM sys.sysprocesses 
	WHERE loginame = @user; 
	
-- set rows to process 
SET @rowsToProcess = @@ROWCOUNT; 

-- loop through SPIDs to kill 
SET @CurrentRow = 0; 
WHILE @CurrentRow < @rowsToProcess
 BEGIN 
 	SET @CurrentRow = @CurrentRow + 1; 
	SELECT @SelectSpid = spid FROM @ReportObjectsTable 
		WHERE rowid = @CurrentRow 
			
	SET @killString = 'Kill ' + CONVERT(VARCHAR, @SelectSpid); 
	
	-- verify spid still exits and belongs to correct user before killing 
	IF EXISTS(SELECT spid, loginame FROM sys.sysprocesses 
		WHERE spid = @SelectSpid AND loginame = @user) 
		
	BEGIN  
		PRINT @killString; -- verificatioin step I used to test against 
		EXEC (@killString) 
	END; 
	ELSE 
		PRINT 'No spid to kill'  
		WAITFOR DELAY '00:00:05'; -- add delay if needed 
END;

On the other side, I scripted out a job to re-enable that could be called from a separate stored procedure at the conclusion of the job.

%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close