force close connections

hello!
is there any way to force the server to close all existing USER connections?
i mean, not the system connections or the sql server agent connections, only
the user ones.
Thanks!!!!
Not directly. Is this for a certain database? If so, perhaps setting database option restricted user
might help. Also, when you set a database option, you have a rollback option, which will kick out
existing users.
If above doesn't suit you, you could always write a cursor which loops something like sysprocesses
and uses dynamic SQL to KILL the spids you don't want to keep.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER connections?
> i mean, not the system connections or the sql server agent connections, only
> the user ones.
> Thanks!!!!
|||On Apr 16, 7:03Xam, DarthSidious
<DarthSidi...@.discussions.microsoft.com> wrote:
> hello!
> is there any way to force the server to close all existing USER connections?
> i mean, not the system connections or the sql server agent connections, only
> the user ones.
> Thanks!!!!
Here is what I use - just pass the database name and run in master:
CREATE PROCEDURE [dbo].[utl_KillUsers] @.dbname varchar(50) as
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
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
|||In sql 2005 you can also set a database to single user mode and use the WITH
ROLLBACK IMMEDIATE flag. See ALTER DATABASE in BOL. If you wanted to
disconnect ALL user connections you would need to do this for every user
database.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DarthSidious" <DarthSidious@.discussions.microsoft.com> wrote in message
news:EAF6BCAC-A658-43B9-AEF7-C16423C43210@.microsoft.com...
> hello!
> is there any way to force the server to close all existing USER
> connections?
> i mean, not the system connections or the sql server agent connections,
> only
> the user ones.
> Thanks!!!!