Allowing end users to kill their own queries

Question

I am an end user (programmer/analyst) without permissions to kill my own queries in our datawarehouse. This causes problems when I need one killed (like right now - 5:30 on a Friday afternoon) and there is no-one around to do it for me.

I'm looking for a solution that would allow me to see a list of my currently running queries and then specify a query to kill. I would only have permission to kill my own queries and no others.

All of my queries are read only - I have no permissions to write to the DB. We access the DB via ODBC from various 3rd party apps, and sometimes from MS SQL Svr Mgmt Studio. Not 100% sure of the server version, but let me know if it's necessary and I can find out.

Is this possible? If so, what downsides exist. Ideally I would like to be able to sell the idea to the DBA team and have them implement it.

Answer 1

Your DBAs can grant you elevated permissions so that you can do this. Normally this wouldn't be needed however. If you are in SQL Server Management Studio you can simply click on the stop button and 99% of the time the query will simply stop (the amount of time that it takes to stop will depend on the amount of data which has been changed by the query as the transaction will need to be rolled back). Failing that you could kill the application on your side which would then terminate the query on the SQL Server when the application is closed.

Answer 2

This is how I get currently active processes running on SQL Server 2008 R2. Results will be the list of queries running for that specific user and then you can apply "kill [session_Id]" command for all processes running or use a cursor loop to kill all sessions.

select sqltext.TEXT, d.session_id, DB_Name(d.database_id) as DatabaseName, s.loginame, d.start_time, d.status, d.command, s.blocked, s.kpid, d.cpu_time, d.total_elapsed_time/1000000 as seconds 
from sys.dm_exec_requests d 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
inner join sys.sysprocesses s  on s.spid = d.session_id 
where s.loginname = {YOUR_END_USER_LOGIN_NAME}

I have never used it from another application but give it a shot. Hope it helps.