August 10, 2003 at 7:57 pm
How can I kick off all users from a particular Database, and keep them off.
Any help much appreciated.
Declan
August 10, 2003 at 10:17 pm
Hi there
See script below, then alter db and set to dbo only, of course, your users dont have dbo privs right? 🙂 see BOL for the syntax. I "think" this script was written by Brian Knight, cant remember, ive had it for a while now... play around with it and customise as need be.
CREATE PROCEDURE usp_KillUsers @dbname varchar(50), @hostname varchar(100) = null 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(100),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname,hostname FROM #tmpUsers WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
DECLARE @userhost varchar(100)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2, @userhost
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if @hostname is null begin
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
end
else if @userhost is not null and @hostname = @userhost begin
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
end
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2, @userhost
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
GO
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 10, 2003 at 10:36 pm
I used to do similar under SQL 7.0, but SQL 2000 made it much easier, with something like:
USE PUBS
ALTER DATABASE Pubs
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
If you want to be a bit less brutal to users currently in the database...
ALTER DATABASE Pubs
SET SINGLE_USER WITH ROLLBACK AFTER 15 SECONDS
And, to let them back on again later...
ALTER DATABASE Pubs
SET MULTI_USER
Cheers,
- Mark
Cheers,
- Mark
August 10, 2003 at 10:51 pm
Mark is right on the button! im stuck in my old v7 ways 🙂
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 2, 2004 at 12:22 pm
I know this is late in reply.....but I'm running into this problem now and found this old post.
One of the catches in
ALTER DATABASE %dbname%
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
is that it won't work on the Master DB. We have one program (N-tier setup) with a web based client and app server SW. The app server connects to the master DB then establishes a connection to the %userDB%. The app server SW logs in to the master and UserDB on the first web interface and doesn't log out until the SQL server or the app server SW is restarted. If I kill the connection, it will relink on its own the next time someone fires the web client. Which no one should be doing between 11P and 6A.
This has been a Royal PITA for backups and integrity checks. Your usp_KillUsers will solve this problem for the system and DB backups. I'll just put it in a a step in the maint plan.
I did make one change....In the
DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname,hostname FROM #tmpUsers WHERE upper(dbname) = upper(@dbname) AND SPID > 50
section I added "AND SPID > 50" in the where clause. Any SPID below 51 is a sql server/system process spid. That way you can safely use it agains master and or any database.
Just my $0.02. Thanks again for the sp.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply