InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:52:11 GMT20http://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxMichael,Very slick! Thank you!JeffTue, 21 Sep 2010 11:56:09 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxVery 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.Mon, 27 Apr 2009 18:14:58 GMTstevekgoodwinhttp://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxThanks alot, I need this just now and it worked perfectly.Wed, 24 Sep 2008 01:14:28 GMTLars Søe Mikkelsenhttp://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxThis is a much simpler way to get everyone out of the database:[code]-- Get rid of all DB users use masteralter database MyDatabase set offline with rollback immediate-- Bring DB back online in single user mode, and connect to DBalter database MyDatabase set online, single_user with rollback immediateuse MyDatabase-- do stuff ---- Return DB to multi user modeuse masteralter database MyDatabase set multi_user with rollback immediate[/code]Fri, 04 Apr 2008 14:56:22 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxNice script!RequestID added to #tmpUsers for SQL2005-----------------------Alter PROCEDURE KillALLUsers @dbname varchar(50) asSET NOCOUNT ONDECLARE @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_WHODECLARE LoginCursor CURSORREAD_ONLYFOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbnameDECLARE @spid varchar(10)DECLARE @dbname2 varchar(40)OPEN LoginCursorFETCH NEXT FROM LoginCursor INTO @spid, @dbname2WHILE (@@fetch_status &lt;&gt; -1)BEGIN IF (@@fetch_status &lt;&gt; -2) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2ENDCLOSE LoginCursorDEALLOCATE LoginCursorDROP table #tmpUsersFri, 04 Apr 2008 14:32:57 GMTSam Hewawasam http://www.sqlservercentral.com/Forums/Topic401827-31-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Lock+and+Connection+Management/30030/">Kill All Users in A Given Database</A>[/B]Sun, 23 Sep 2007 21:35:25 GMTBrian Knight