Kill user process

Question

Currently, i have SQL Server 2005 database for which I have admin rights, but do not have sysadmin rights.

There was a particular process which was stopping me to do few things on a table.

Is there a way to kill a user SQL queries, if they were ran through a web client without having sys admin rights. In my case, django website ran the query using pymssql ?

Answer 1

Using "KILL" is role-restricted for a very good reason, and should be used only when you understand what the blocking process is doing. If the KILL statement is used against a SPID executing a long-running transaction, the KILL will initiate a ROLLBACK on the table (which can take quite a while on a large table) to maintain DB consistency. The statement:

SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>

will give you some detail about the process and what it's doing. Alternatively you can try:

DBCC INPUTBUFFER(<spid>)

to find the last statement that was submitted by a SPID (though I think your permissions might not be sufficient). If it's determined that the SPID is sleeping on the block, you'll still need to be promoted to a minimum role of processadmin in order to execute a KILL statement.

Three other recommendations to help you:

1. Work to implement the use of Locking Hints in code executed against the DB.
2. If this is a consistent problem, ask your DBA's to evaluate the statement(s) that cause the blocks, and ask their advice.
3. Getting close to your DBAs never hurts, and a little buttering up (pizza lunch, after-work drinks, etc.) once or twice can really make a difference. They may not grant you the permission promotion you need, but they could create a workaround for you as described here that could solve your problem without changing your role.