Kill All Users in A Given Database

  • Brian Knight

    One Orange Chip

    Points: 27249

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

  • Sam Hewawasam

    SSC Journeyman

    Points: 87

    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

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    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

  • Lars Søe Mikkelsen

    SSCommitted

    Points: 1602

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

  • stevekgoodwin

    SSC Enthusiast

    Points: 116

    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.

  • JRoughgarden

    Ten Centuries

    Points: 1119

    Michael,

    Very slick! Thank you!

    Jeff

  • med_haddad

    Valued Member

    Points: 71

    ¿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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply