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

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.

Answer 3

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.