5

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.

5
  • 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, 2013 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. Jan 13, 2013 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. Jan 14, 2013 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). Jan 14, 2013 at 15:26
  • @AaronBertrand Haha yeah it certainly is poor design. SAS is a 4GL statistics package similar to SPSS or R. Jan 14, 2013 at 15:28

3 Answers 3

5

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.

4
  • 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. Jan 13, 2013 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, 2013 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). Jan 14, 2013 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, 2013 at 0:32
2

Do the following to create a certificate-based login with the rights necessary to view and kill SPIDs:

-- =============================================
-- Create AuthorizedKiller Login and Certificate
-- =============================================
-- This script creates the secure certificate and associated login.
-- <Password goes here> should be replaced with the an appropriate password.
-- This script should be run once only.

-- Create the certificate in the database and back it up to a file.
USE [DATABASE]
GO

CREATE CERTIFICATE [AuthorizedKillerCertificate] 
ENCRYPTION BY PASSWORD = '<Password goes here>' 
WITH SUBJECT = 'Certificate for signing Stored Procedure',
EXPIRY_DATE = '12/1/2099'; 
GO

-- This will not overwrite the file. If it exists, you must log on to the server and delete it first.
BACKUP CERTIFICATE [AuthorizedKillerCertificate] 
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AuthorizedKillerCertificate.CER'; 
GO


-- Copy the certificate to the master database and create a login associated with it.
USE [master] 
GO 

CREATE CERTIFICATE [AuthorizedKillerCertificate] 
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AuthorizedKillerCertificate.CER'; 
GO

CREATE LOGIN [AuthorizedKiller] 
FROM CERTIFICATE [AuthorizedKillerCertificate]; 
Go

GRANT AUTHENTICATE SERVER TO [AuthorizedKiller]; 
GO 

-- Grant the roles necessary to view and kill tasks.
GRANT VIEW SERVER STATE TO [AuthorizedKiller]
GO

GRANT ALTER ANY connection TO [AuthorizedKiller]
GO

-- Create a user associated with the login in the database.
USE [DATABASE] 
GO 

CREATE USER [AuthorizedKiller] FROM LOGIN [AuthorizedKiller] 
GO 

Create a stored procedure:

CREATE PROCEDURE [dbo].[RestrictedKillProcedure]
-- Add arguments here.
BEGIN
-- Add restricted kill code here.
END 

After creating the stored procedure with appropriate code to restrict kills, sign it and grant permission to a role to execute it:

ADD SIGNATURE TO OBJECT::[RestrictedKillProcedure] 
BY CERTIFICATE [AuthorizedKillerCertificate]  
WITH PASSWORD = '<Password goes here>'; 
GO 

GRANT EXECUTE ON [RestrictedKillProcedure] TO [AuthorizedKillerRole]

Finally, add the user to the role.

2
  • Given the user requirements, they need to kill theirs and only their process without affecting others, I think this is the best approach and one that I was starting to write when I saw it appear here. This way, the user can execute the stored proc, or have something external execute the stored proc, passing the user credentials along and using the stored credentials that has the needed permissions. Well done!!! Aug 25, 2016 at 19:29
  • @ShooterMcGavin might you be able to share the code you wound up using? Just ran into a situation where I need the exact same thing. Thanks.
    – mbourgon
    Apr 24, 2018 at 15:40
0

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.

6
  • 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? Jan 15, 2013 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, 2013 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. Jan 17, 2013 at 18:22
  • How about using s.loginame = SYSTEM_USER instead?
    – avakharia
    Jan 17, 2013 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. Jan 17, 2013 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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