script to kick all users off database

I am writing a script to delete all user tables from a SQL Server 2000
database and before I do that I want to kick all other users off the
database.
Can anyone give me the sql script that will do this?
Killing all the connections to the database you can use command:-
ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Vishal,
That also puts it in single user mode, which might not be desirable.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Vishal Parkar wrote:
> Killing all the connections to the database you can use command:-
> ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE
>
|||Caroline,
This is what I use:
CREATE proc sp_dba_killscript
@.dbname sysname
as
select 'kill '+ convert(varchar(5),a.spid)
from master..sysprocesses a join master..sysdatabases b
on (a.dbid=b.dbid)
where b.name = @.dbname
Paste the result into your query window and run it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Caroline wrote:
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?
|||There is a nice script that Tibor wrote, that will send a message to
everyone logged in to the server. So you can before killing... His web site
is www.dbmaint.com
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Caroline" <carolinefryer@.gmail.com> wrote in message
news:eea6e29c.0411091656.1ee19f46@.posting.google.c om...
> I am writing a script to delete all user tables from a SQL Server 2000
> database and before I do that I want to kick all other users off the
> database.
> Can anyone give me the sql script that will do this?