# Tuesday, June 26, 2007

SQL Server - Kill all users script

Found this the other day - for when you're restoring SQL databases.  You need to kick out all current users right?

Run this little fella into your master database and away you go - just run this step before your restore.

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON

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

CREATE table #tmpUsers(
spid int,
dbname varchar(50))

INSERT INTO #tmpUsers
SELECT 
[Process ID]    = l.req_spid, db_name(l.rsc_dbid)
from master.dbo.syslockinfo l with (NOLOCK) 
where db_name(l.rsc_dbid) = @dbname
group by l.req_spid, db_name(l.rsc_dbid)


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
Colorized by: CarlosAg.CodeColorizer
SQL Server
posted on Tuesday, June 26, 2007 8:15:39 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
Related posts:
Generate SQL Server Inserts from existing data - with Identity Inserts
Use SQL Server Trusted Connections with ASP.NET on Windows 2003 without impersonation
SQL Server - Insert to table with ALL default values
SQL Server Stored Procedure to disable / enable all foreign keys and constraints in a database
HOW TO Use SQL Server DTS Transform Data Task to export to dynamic Excel spreadsheet file
Using local SQL 2005 Express databases, |DataDirectory| setting and SQLBulkCopy with SemiColon delimited text files
 Tracked by:
http://yesihavemoneyy.com [Pingback]
http://realvideopornoo.com [Pingback]
http://tubepornoss.com [Pingback]
http://morningside.edu/mics/_notes/pages/claritin/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/celebrex/index.html [Pingback]
http://blastpr.com/wiki/js/pages/clomid/index.html [Pingback]
http://blastpr.com/wiki/js/pages/lexapro/index.html [Pingback]
http://blastpr.com/wiki/js/pages/melatonin/index.html [Pingback]
http://blastpr.com/wiki/js/pages/ultram/index.html [Pingback]
http://blastpr.com/wiki/js/pages/hoodia/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/celexa/index.html [Pingback]
http://blastpr.com/wiki/js/pages/nexium/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/effexor/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/rainbow-brite/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/cialis/index.html [Pingback]
http://blastpr.com/wiki/js/pages/zoloft/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/soma/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/nexium/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/tramadol/index.html [Pingback]
http://blastpr.com/wiki/js/pages/cialis/index.html [Pingback]
http://blastpr.com/wiki/js/pages/cymbalta/index.html [Pingback]
http://blastpr.com/wiki/js/pages/prozac/index.html [Pingback]
http://blastpr.com/wiki/js/pages/coumadin/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/prozac/index.html [Pingback]
http://blastpr.com/wiki/js/pages/rainbow-brite/index.html [Pingback]
http://blastpr.com/wiki/js/pages/soma/index.html [Pingback]
http://blastpr.com/wiki/js/pages/tramadol/index.html [Pingback]
http://blastpr.com/wiki/js/pages/celexa/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/coumadin/index.html [Pingback]
http://blastpr.com/wiki/js/pages/claritin/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/clomid/index.html [Pingback]
http://blastpr.com/wiki/js/pages/lipitor/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/accutane/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/ultram/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/prilosec/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/lipitor/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/synthroid/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/lexapro/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/melatonin/index.html [Pingback]
http://morningside.edu/mics/_notes/pages/wellbutrin/index.html [Pingback]
http://ipsilon.hr/ipsilon.hr/cms/4/lib/docs/24066563/index.html [Pingback]
http://discussgod.com/cpstyles/docs/25383456/index.html [Pingback]
http://temerav.com/images/menu/05559064/index.html [Pingback]
http://split-dalmatia.com/split-dalmatia.com/images/docs/84431573/index.html [Pingback]
http://discussgod.com/cpstyles/docs/43932298/index.html [Pingback]
http://legambitdufou.org/Library/docs/15090396/index.html [Pingback]
http://islands-croatia.comislands-croatia.com/includes/js/docs/60974094/index.ht... [Pingback]
http://realestate.hr/templates/css/docs/71546796/index.html [Pingback]
http://thebix.com/includes/compat/docs/15870923/index.html [Pingback]
http://lecouac.org/ecrire/lang/docs/25282359/index.html [Pingback]
http://pspdesktops.com/fileupload/store/docs/18769945/index.html [Pingback]
http://ipsilon.hr/ipsilon.hr/cms/4/lib/docs/55227677/index.html [Pingback]
http://swellhead.netswellhead.net/docs/05235252/index.html [Pingback]
http://ncdtnanotechportal.info/generator/docs/87198700/index.html [Pingback]
http://martinrozon.com/images/photos/docs/75270452/index.html [Pingback]
http://islands-croatia.comislands-croatia.com/includes/js/docs/82710340/index.ht... [Pingback]
http://ncdtnanotechportal.info/generator/docs/13227634/index.html [Pingback]
http://promocija.com.hr/promocija.com.hr/includes/js/docs/48335156/index.html [Pingback]
http://add2rss.com/img/design/docs/45658867/index.html [Pingback]
http://allfreefilms.com/wp-includes/js/27702077/index.html [Pingback]
http://swellhead.netswellhead.net/docs/92808772/index.html [Pingback]
http://jivest2006.com/docs/40579018/index.html [Pingback]
http://split-dalmatia.com/split-dalmatia.com/images/docs/34320152/index.html [Pingback]
http://islands-croatia.comislands-croatia.com/includes/js/docs/06712704/index.ht... [Pingback]
http://coolioness.com/attachments/docs/83777724/index.html [Pingback]
http://blog.netmedia.hr/wp-includes/js/docs/84238305/index.html [Pingback]
http://thejohnslater.com/pix/img/docs/41914710/index.html [Pingback]
http://witze-humor.de/templates/images/docs/83157240/index.html [Pingback]
http://thejohnslater.com/pix/img/docs/56008043/index.html [Pingback]
http://islands-croatia.comislands-croatia.com/includes/js/docs/87090382/index.ht... [Pingback]
http://vladan.strigo.net/wp-includes/js/docs/86309858/index.html [Pingback]
http://split-dalmatia.com/split-dalmatia.com/images/docs/73811526/index.html [Pingback]
http://entartistes.ca/images/images/docs/81367526/index.html [Pingback]
http://temerav.com/images/menu/46200403/index.html [Pingback]
http://coolioness.com/attachments/docs/76375390/index.html [Pingback]
http://lecouac.org/ecrire/lang/docs/77066936/index.html [Pingback]
http://promocija.com.hr/promocija.com.hr/includes/js/docs/37348396/index.html [Pingback]
http://blog.netmedia.hr/wp-includes/js/docs/91708760/index.html [Pingback]
http://realestate.hr/templates/css/docs/36157459/index.html [Pingback]
http://seo4u.at/images/docs/68897595/index.html [Pingback]
http://discussgod.com/cpstyles/docs/90092602/index.html [Pingback]
http://pddownloads.com/docs/21991908/index.html [Pingback]
http://promocija.com.hr/promocija.com.hr/includes/js/docs/36483653/index.html [Pingback]
http://blog.netmedia.hr/wp-includes/js/docs/44378735/index.html [Pingback]
http://discussgod.com/cpstyles/docs/73291253/index.html [Pingback]
http://swellhead.netswellhead.net/docs/42306518/index.html [Pingback]
http://thejohnslater.com/pix/img/docs/73486930/index.html [Pingback]
http://adventure-traveling.com/images/img/viagra/ [Pingback]
http://easytravelcanada.info/js/pages/3/clomid/ [Pingback]
http://easymexico.info/images/img/cialis/ [Pingback]
http://sevainc.com/bad_denise/img/7/nexium/ [Pingback]
http://adventure-traveling.com/images/img/cialis/ [Pingback]
http://abaffydesign.com/la/img/cialis/ [Pingback]
http://easytravelcanada.info/js/pages/2/celexa/ [Pingback]
http://easytravelcanada.info/js/pages/12/wellbutrin/ [Pingback]
http://sevainc.com/bad_denise/img/5/hoodia/ [Pingback]
http://sevainc.com/bad_denise/img/4/coumadin/ [Pingback]
http://sevainc.com/bad_denise/img/4/cymbalta/ [Pingback]
http://sevainc.com/bad_denise/img/2/celexa/ [Pingback]
http://easytravelcanada.info/js/pages/7/melatonin/ [Pingback]
http://jemnemelodierecords.sk/img/viagra/ [Pingback]
http://easycanada.info/js/pages/viagra/ [Pingback]
http://easytravelcanada.info/js/pages/2/cialis/ [Pingback]
http://inatelevizia.sk/ad/img/viagra/ [Pingback]
http://easytravelcanada.info/js/pages/9/rainbow-brite/ [Pingback]
http://abaffydesign.com/la/img/viagra/ [Pingback]
http://sevainc.com/bad_denise/img/12/wellbutrin/ [Pingback]
http://easytravelcanada.info/js/pages/5/hoodia/ [Pingback]
http://simpletravelcanada.info/js/pages/27277365/ [Pingback]
http://simplecanada.info/js/pages/13912893/ [Pingback]
http://easycanada.info/js/pages/cialis/ [Pingback]
http://sevainc.com/bad_denise/img/1/celebrex/ [Pingback]
http://easytravelcanada.info/js/pages/10/synthroid/ [Pingback]
http://easytravelcanada.info/js/pages/4/coumadin/ [Pingback]
http://easytravelcanada.info/js/pages/8/paxil/ [Pingback]
http://sevainc.com/bad_denise/img/10/soma/ [Pingback]
http://easytravelcanada.info/js/pages/6/lipitor/ [Pingback]
http://easytravelcanada.info/js/pages/8/prilosec/ [Pingback]
http://ina-tv.sk/img/viagra/ [Pingback]
http://easytravelcanada.info/js/pages/6/lexapro/ [Pingback]
http://easytravelcanada.info/js/pages/10/soma/ [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/statistics-on-teens-allowan... [Pingback]
http://odin.net/images/pages/35694472/index.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/st-pauli-girl-distribution... [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/hot-russian-models-teen-age... [Pingback]
http://odin.net/images/pages/35694472/celeb-up-skirts.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/baby-shower-graphics.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/teen-trends-courtney-doll.h... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/la-blue-girl-free-download... [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/straight-guys-fuck-gays-for... [Pingback]
http://odin.net/images/pages/52807681/lulla-smith-moses-baby-ensemble.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/marriage-sex-life.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/teen-doggystyle-fucking-fr... [Pingback]
http://odin.net/images/pages/52807681/sex-and-deviltry.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/sexual-xxx-stories.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/foot-fetish-video-s.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/anal-sex-shemale.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/adult-free-gay-porn.html [Pingback]
http://odin.net/images/pages/35694472/free-amauter-porn.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/fuck-bitches-get-money-lyri... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/nude-celeb-thumbs.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/porn-star-brooke-banner.ht... [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/nauty-celebritys-having-sex... [Pingback]
http://odin.net/images/pages/35694472/xxx-message-boards.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/a1-thumbnails-posts.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/teen-girl-preview.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/the-internet-is-for-porn.h... [Pingback]
http://odin.net/images/pages/52807681/cheerleaders-sex-towel.html [Pingback]
http://odin.net/images/pages/52807681/marathon-florida-webcam.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/anime-preteen-sex.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/adult-bib.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/what-is-the-mature-ripened... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/all-pure-nude-teens-photos... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/53348735/andy-kim-baby-i-love-you.h... [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/lisa-rowe-girl-interrupted.... [Pingback]
http://odin.net/images/pages/35694472/baby-got-back-by-throwdown.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/porn-pictures-of-girls.htm... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/mature-fucking-movies.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/xxx-asian-anal-milf-free.h... [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/teenage-girl-nude.html [Pingback]
http://odin.net/images/pages/35694472/should-teens-date-seriously.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/99493954/sex-gadis-melayu.html [Pingback]
http://odin.net/images/pages/52807681/sixteen-inch-penis.html [Pingback]
http://odin.net/images/pages/52807681/boys-and-girls-grinding.html [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/brandi-may-pics.html [Pingback]
http://odin.net/images/pages/35694472/council-of-adult-education-australia.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/camping-naturisten-free-pi... [Pingback]
http://odin.net/images/pages/52807681/aurora-snow-xxx.html [Pingback]
http://gatewayplayhouse.com/photos/cai/pages/35807953/britney-spears-nude-crotch... [Pingback]
http://cidesi.com/images/metro/metro2/pages/32162341/blonde-porn-star.html [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-soma-online.html [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-viagra-online.html [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-phentermine-online.ht... [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-vicodin-online.html [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-ambien-online.html [Pingback]
http://www.signalprocessingsociety.org/community/forum/buy-tramadol-online.html [Pingback]
Comments are closed.