Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
we can do in MSSQL server by getting spid of a particular user and kill that session , but it wont be good thing giving permissions to users for Killing spids... i think your dba's can do it if they are ok for this one. –  kumar_2002 Jan 12 '13 at 2:06
    
Do you have queries running on workstations that you aren't able to access? I'm not sure why you don't have permission to stop your own queries from running. –  Aaron Bertrand Jan 13 '13 at 2:36
    
@AaronBertrand The main program we are using for submitting queries is SAS and 'interupting' SAS will only stop the SAS code once control has returned to it (ie. after the query completes). We could close the SAS program (losing work/progress) but this also tends to leave the queries running on the server. When we connect to mySQL DBs we can kill our own SAS queries via a 3rd party tool and as soon as the query dies control returns to SAS without us losing any work in progress. I was hoping for something similar to our mySQL abilities. –  Rob Penridge Jan 14 '13 at 15:21
1  
Well you can identify your own queries in sys.dm_exec_requests to identify the session_id(s) to kill, say by hostname or login name, but I don't know of any way to make the permissions granular enough to only identify your own sessions that way. That seems like very unfriendly behavior by SAS (sorry, I have no idea what that acronym means). –  Aaron Bertrand Jan 14 '13 at 15:26
    
@AaronBertrand Haha yeah it certainly is poor design. SAS is a 4GL statistics package similar to SPSS or R. –  Rob Penridge Jan 14 '13 at 15:28

2 Answers 2

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.

share|improve this answer
1  
Well to add a bit of info, it won't always "simply stop" ... it might sometimes take a long time to cancel, depending on what was running and how much work it will take to roll back. –  Aaron Bertrand Jan 13 '13 at 2:34
    
Added a note about that. Given that his queries are all SELECT only they "should" roll back pretty quickly. –  mrdenny Jan 13 '13 at 19:27
    
Thanks - yes this is a good solution for SQL Svr Mgmt Studio but I still need a solution for the other tools that we use (see my comment to AaronBertrand on the original post for more detail). –  Rob Penridge Jan 14 '13 at 15:26
    
There's not going to be any permission that the DBA can grant which will allow you to do this using native T-SQL. They'll need to build an app (something web based would be easy enough) which would show you all of your queries and allow you to kill them via that app. –  mrdenny Jan 18 '13 at 0:32

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.

share|improve this answer
    
I mentioned this solution in a comment several hours ago. The problem is that the OP requires that the user only has permission to kill their own sessions; this seems to require higher permissions than that. As an aside, why are you using sys.sysprocesses, a deprecated backward compatibility view, instead of the DMV sys.dm_exec_requests? –  Aaron Bertrand Jan 15 '13 at 3:14
    
You can replace the login name in where clause with the user's login name and that should do it. And about sys.sysprocesses, I've just used it and it works for me but you can use the sys.dm_exec_requests as well or even sys.dm_exec_sessions. As I said I've never used it exactly like he wants, so it's just a suggestion. –  avakharia Jan 17 '13 at 18:15
    
Again I think you're missing the point. I know that in your query you can kill any username you enter in the query, and that's a right he doesn't want to grant individual users, precisely because they could kill any username they enter in the query. He wants any user to only be able to kill their own runaway queries. –  Aaron Bertrand Jan 17 '13 at 18:22
    
How about using s.loginame = SYSTEM_USER instead? –  avakharia Jan 17 '13 at 18:34
2  
<sigh> It's not about matching rows in that query, so it really doesn't matter what you put in the WHERE clause (and you can't force your co-worker to put SYSTEM_USER in their WHERE clause anyway). It's about being able to execute the KILL command after you've run the query - you can't force KILL to only affect sessions associated with SYSTEM_USER. –  Aaron Bertrand Jan 17 '13 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.