Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Kill All Users in A Given Database Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 9:35 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
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
Posted Friday, April 4, 2008 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 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
Posted Friday, April 4, 2008 2:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:31 PM
Points: 3,116, Visits: 11,388
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
Posted Wednesday, September 24, 2008 1:14 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:17 AM
Points: 70, Visits: 472
Thanks alot, I need this just now and it worked perfectly.
Post #574934
Posted Monday, April 27, 2009 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 3:03 PM
Points: 2, Visits: 32
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
Posted Tuesday, September 21, 2010 11:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Michael,

Very slick! Thank you!

Jeff



Post #990624
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse