Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    33

    Unanswered: Is there a SP that will logout users from a database?

    Hi,

    I looked everywhere in System Stored Procedure docs for MSDE 2005 and I cant find a stored procedure that will force logout users from a database.

    Is there such a thing? If not, is there another way?

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on what you mean by "force logout" check either KILL OR ALTER DATABASE ... WITH ROLLBACK IMMEDIATE.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you sa on the box?

    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2003
    Posts
    33
    hmm... what you see here is what i get from the sp_who command followed by the sp_lock command. I "looks" like I can figure out a way to identify process 52 and then KILL it, but its not going to be simple. thanks guys.

    (processes 1-50 are system processes)
    51 0 sleeping
    QOR\williams
    QOR
    master
    AWAITING COMMAND 0
    52 0 runnable
    QOR\williams
    QOR
    master
    SELECT 0

    (21 rows affected)
    1> exec sp_lock
    2> go
    spid dbid ObjId IndId Type Resource Mode Status
    ------ ------ ----------- ------ ---- -------------------------------- -------- ------
    52 1 1115151018 0 TAB IS GRANT

  5. #5
    Join Date
    Feb 2003
    Posts
    33
    I discoverd that sp_who2 is way better than sp_who.

    Ok, excuse the totally invalid SQL language below, but what I REALLY REALLY need is something like this:

    KILL spid of processes where exec sp_lock.DBName = "aaa"

    Is this possible with a osql command? Am I overlooking an easy way to do this or do I need to do it a hard way?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've got an "old friend" that I haven't used in a long time, but I recreated from memory. It might help you, but I'll leave the decision of whether to use it or not up to you!
    Code:
    IF EXISTS (SELECT * FROM dbo.sysobjects AS o WHERE 'p_KillCulprits' = name) DROP PROCEDURE p_KillCulprits
    GO
    --  ptp  20071003  Kill spids that are blocking others, but not blocked
    
    CREATE PROCEDURE p_KillCulprits
    AS
    
    DECLARE @cCmd			VARCHAR(40)
    
    DECLARE zCulprits CURSOR FOR
    SELECT 'KILL ' + CAST(c.spid AS VARCHAR(6))
       FROM master.dbo.sysprocesses AS c		-- Culprit
       WHERE  0 = c.blocked				-- Is not blocked
          AND EXISTS (SELECT *			-- and blocks at least one spid
             FROM master.dbo.sysprocesses AS v
             WHERE  v.blocked = c.spid
                AND v.blocked != v.spid)		-- Watch for sp2 "feature" !
    
    OPEN zCulprits
    FETCH zCulprits INTO @cCmd
    
    WHILE 0 = @@fetch_status
       BEGIN
          EXECUTE (@cCmd)
          FETCH zCulprits INTO @cCmd
       END
    
    CLOSE zCulprits
    DEALLOCATE zCulprits
    
    RETURN
    GO
    -PatP

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    We had a huge problem here with blocking locks on a sh*tty system called Advisorware, the nolock hint cleared that problem right up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •