Stored Procedure to disconnect all users from a SQL Server database
This is one of the Stored Procedures I use frequently:
CREATE PROCEDURE sp_kill_database_users @arg_dbname sysname with recompile
AS
-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000
declare @a_spid smallint
declare @msg varchar(255)
declare @a_dbid int
select @a_dbid = sdb.dbid
from master..sysdatabases sdb
where sdb.name = @arg_dbname
declare db_users insensitive cursor for
select
sp.spid
from master..sysprocesses sp
where sp.dbid = @a_dbid
open db_users
fetch next from db_users into @a_spid
while @@fetch_status = 0
begin
select @msg = 'kill '+convert(char(5),@a_spid)
print @msg
execute (@msg)
fetch next from db_users into @a_spid
end
close db_users
deallocate db_users
GO
CREATE PROCEDURE sp_kill_database_users @arg_dbname sysname with recompile
AS
-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000
declare @a_spid smallint
declare @msg varchar(255)
declare @a_dbid int
select @a_dbid = sdb.dbid
from master..sysdatabases sdb
where sdb.name = @arg_dbname
declare db_users insensitive cursor for
select
sp.spid
from master..sysprocesses sp
where sp.dbid = @a_dbid
open db_users
fetch next from db_users into @a_spid
while @@fetch_status = 0
begin
select @msg = 'kill '+convert(char(5),@a_spid)
print @msg
execute (@msg)
fetch next from db_users into @a_spid
end
close db_users
deallocate db_users
GO
1 Comments:
Great!
Thanx
By Jim, at 8:50 AM
Post a Comment
<< Home