September 23, 2007 at 9:35 pm
Comments posted to this topic are about the item Kill All Users in A Given Database
Brian Knight
Free SQL Server Training Webinars
April 4, 2008 at 2:32 pm
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
April 4, 2008 at 2:56 pm
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
September 24, 2008 at 1:14 am
Thanks alot, I need this just now and it worked perfectly.
April 27, 2009 at 6:14 pm
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.
September 21, 2010 at 11:56 am
Michael,
Very slick! Thank you!
Jeff
February 13, 2015 at 3:28 am
¿And if the connection to @dbname comes from a view of another database on the same server?
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
¡¡appear only direct connections!! :doze:
the connection to the VIEW will return the database to which it belongs , not to the database where are accessing
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply