CREATE PROCEDURE Kill_Process
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS
SET NOCOUNT ON;	
DECLARE @spid smallint;
DECLARE @spid2 smallint;
DECLARE @loginame nchar(128);
DECLARE @nsql NVARCHAR(4000);
SET @loginame = 'xxxx'
	
EXEC Find_Lock_Info @DBName, @TableName

DECLARE ProcessCursor CURSOR FOR 
SELECT spid FROM  master.dbo.sysprocesses  
WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid
AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND 	OBJECT_NAME(ObjId) = @TableName)
	

	OPEN ProcessCursor;		
	FETCH NEXT FROM ProcessCursor INTO @spid;
	WHILE @@FETCH_STATUS = 0
	BEGIN				
		SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + '';
		EXEC sp_executesql @nsql;
		FETCH NEXT FROM ProcessCursor INTO @spid;
	END	
	CLOSE ProcessCursor;	
	DEALLOCATE ProcessCursor;
GO


CREATE PROCEDURE Find_Lock_Info
@DBName VARCHAR(100),
@TableName VARCHAR(1000)
AS

SET NOCOUNT ON

BEGIN
        CREATE TABLE  #tmpLockInfo
        (
        spid                		SMALLINT,
        dbid          			SMALLINT,
        ObjId 				INT,
        IndId				SMALLINT,
        Type                		NCHAR(4),
        Resource			NCHAR(32),
        Mode				NVARCHAR(8),
        Status				NVARCHAR(5)       
        )

       INSERT INTO #tmpLockInfo
        (
       spid, dbid, ObjId, IndId, Type, Resource, Mode, Status
        ) 
        EXEC Lock_Info

      TRUNCATE TABLE tbl_Lock_Info

       INSERT tbl_Lock_Info
        SELECT
                spid, dbid, ObjId, Type, Status      
        FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName  
   

END
GO

quote:

---

Originally posted by nr

You want to give a user permission to use the kill statement?

You could run the proc from a scheduled task as sysadmin in response to the user adding a row to a table - just schedule it to run every minute looking for a row in the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---