IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[StopLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[StopLogin]
GO
CREATE PROCEDURE StopLogin
@Dname varchar(50)
AS
DECLARE
@name varchar(50),
@s varchar(1000)
BEGIN
IF (@Dname = '')
BEGIN
DECLARE DataName CURSOR FOR
SELECT name FROM sysdatabases WHERE name not in ('master')
OPEN DataName
FETCH NEXT FROM DataName
INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE tb CURSOR local
FOR
SELECT N'kill '+CAST(spid AS varchar)
FROM master..sysprocesses
WHERE dbid=db_id(@name)
OPEN tb
FETCH next FROM tb INTO @s
WHILE @@FETCH_STATUS=0
BEGIN
EXEC(@s)
FETCH NEXT FROM tb INTO @s
END
CLOSE tb
DEALLOCATE tb
FETCH NEXT FROM DataName
INTO @name
END
CLOSE DataName
DEALLOCATE DataName
END
ELSE
BEGIN
DECLARE tb CURSOR local
FOR
SELECT N'kill '+CAST(spid AS varchar)
FROM master..sysprocesses
WHERE dbid=db_id(@Dname)
OPEN tb
FETCH next FROM tb INTO @s
WHILE @@FETCH_STATUS=0
BEGIN
EXEC(@s)
FETCH NEXT FROM tb INTO @s
END
CLOSE tb
DEALLOCATE tb
END
END
StopLogin Hrserver