Kill All Users in A Given Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Knight » Kill All Users in A Given Database

Kill All Users in A Given Database

Rate Topic

Display Mode

Topic Options

Author

Message

Brian Knight

Posted Sunday, September 23, 2007 9:35 PM

SSCommitted

Group: Moderators
Last Login: Monday, August 13, 2012 1:06 PM
Points: 1,928, Visits: 224

Comments posted to this topic are about the item Kill All Users in A Given Database

Brian Knight
Free SQL Server Training Webinars

Post #401827

Sam Hewawasam

Posted Friday, April 04, 2008 2:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 01, 2009 12:20 PM
Points: 1, Visits: 11

Nice script!

RequestID added to #tmpUsers for SQL2005

-----------------------
Alter PROCEDURE KillALLUsers @dbname varchar(50) as
SET NOCOUNT ON

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
request_id int)

INSERT INTO #tmpUsers EXEC SP_WHO

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers

Post #480276

Michael Valentine Jones

Posted Friday, April 04, 2008 2:56 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 2,969, Visits: 10,615

This is a much simpler way to get everyone out of the database:

-- Get rid of all DB users 
use master
alter database MyDatabase set offline with rollback immediate


-- Bring DB back online in single user mode, and connect to DB
alter database MyDatabase set online, single_user with rollback immediate
use MyDatabase

-- do stuff --


-- Return DB to multi user mode
use master
alter database MyDatabase set multi_user with rollback immediate

Post #480296

Lars Søe Mikkelsen

Posted Wednesday, September 24, 2008 1:14 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 23, 2013 5:28 AM
Points: 68, Visits: 426

Thanks alot, I need this just now and it worked perfectly.

Post #574934

stevekgoodwin

Posted Monday, April 27, 2009 6:14 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:35 PM
Points: 1, Visits: 22

Very handy sp, i modified it so it validates the database name parameter against a whitelist of DBs we want to allow this to run against.

Always use whitelists. Blacklists eventually result in something happening accidentally because you forgot to maintain the list.

Post #705446

JRoughgarden

Posted Tuesday, September 21, 2010 11:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 6:07 PM
Points: 36, Visits: 167

Michael,

Very slick! Thank you!

Jeff

Post #990624

« Prev Topic | Next Topic »

Permissions