How to kill a query without dbo rights?

[handbrake]

We are trying to find a way that we can get our DBAs to agree to that will allow us to KILL long running queries from our application code.

In the dev environment life is relatively easy - you can execute a KILL against the spid of your session as we have dbo rights.

However in UAT/production of course our application servers run under a database role which has far more limited permissions, amongst which is the lack of permission to execute a KILL.

I cannot believe we are the only people to hit this problem and desire a solution. Our N-tier application is written in .NET. We cannot use the "Cancel" feature of ADO.Net, because we have multiple application servers servicing the clients using a (highly customised) version of the Microsoft Data Access App Block. Ado.Net Cancel requires "holding on" to your SqlCommand object reference - something which we don't do as it is abstracted. Even if we were to put some nasty in like a static cache of active SqlCommand objects of course in a load balanced server scenario the request to cancel from the client may not even come back to the same server.

So that left us with an idea to store the spid information in the database with a unique query id which is returned to the client - the idea being that if the client issues a cancel request it passes that query id, we open a new session, look up the spid from our running query table for that query id and if the session still is running then "KILL" it.

As stated above that last bit is the sticking point. In an ideal world we would have some extended stored proc which the DBAs own that we are able to call passing the spid, that is a wrapper around KILL. However is it even possible to write and permission a sproc to do what we want (i.e. be called by an account with a different role that does not have that KILL permission)?

The only other alternative that has been mooted is to have a separate "service" application, owned by the DBAs which runs with sufficient dbo priveleges and exposes via a web service for instance the ability to kill a spid on a database. However that is obviously dramatically increasing the complexity of the overall solution and there are other issues it opens up internally over security, ownership, resourcing, hosting etc etc.

Desperately hoping someone out there has some suggestions...

 

[ptheriault]

The best advise I can give you here is "Don't do it!." you are opening up a big can of worms! If you have a process that needs to be killed because it is long running contact the DBA. You will run into a whole bunch of problems if you start allowing people to kill spids. That's why the kill command is a sysadmin funtion.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[handbrake]

Hi Paul,

To date we have done as you say and not allowed such functionality. However all it takes is a couple of screens being abused by users or a poorly indexed table by developers to bring a production app to it's knees.

As for "contact the dba" - if only it were that easy mate. We have so much red tape and perhaps under resourcing at where I work we basically need to request weeks in advance to get any form of "contact", let alone the grumpy responses about trying to get a spid killed on a production database. So one less thing the DBAs have to do for us is something they are interested in as well as us, assuming all necessary controls around it were in place.

Besides which, we would like to build this into certain screens in our applications to make them more user-responsive and user-friendly. Not dissimilar in many ways to Sql Query analyzer that developers can happily hit the "stop" button on. Having the cancel button hooked into a dialog which says "Please contact this person who will in turn spend far too long filling in paperwork, tracking down and begging to a DBA to do this for you" doesn't have the same "user friendly" ring to it... ;-)

A user will only be able to cancel a query they had launched themselves, and the whole functionality as I said is limited to a few read-only non-transactional browser screens where the queries are very dynamic based on masses of optional filter criteria by the user. Replication isn't an option.

So - if you can humour me a little longer and accept we might have a need, how would you go about it? I'm just looking for options at this point to go back to our DBAs with. Sure, the whole thing may well come to nothing if there isn't a solution everyone is happy with. However I can't believe there aren't other people out there battling with the same issues (certainly there are multiple teams within our company that are). Other than running our application as sysadmin which obviously is not going to happen what other options do we have?...

 

[mrdenny]

One big question, what version of SQL Server? Is it 2000 or 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[handbrake]

Well, we use 2000 currently but undoubtedly we will move to 2005 at some point. So if you have a suggestion for either it would be welcomed...

 

[mrdenny]

For SQL 2000 you are pretty much screwed. With SQL 2005 you can use the EXECUTE AS command to elevate the permissions to dbo so that you can run the kill command then revert back to the lower lever of rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[handbrake]

Denny,

That sounds very interesting and exactly what I am after.

Would you mind elaborating a little on how this could work? I apologise as not being a DBA or having used 2005 I dont know it's security model all that well. Are you saying it would be possible to create a "KillProcess" sproc presumably created/executing under some DBA owned account in the "processadmin" role, which can be called by some other account not in that role?

I started reading about this in BOL and got confused very quickly... ;-)

Many thanks for your time.

 

[mrdenny]

In SQL 2005 you can within a stored procedure execute code as a different user by using the execute as command. The execute as must be done as a user within the database so you can pick up there database permissions. I'm not sure if you would pick up server level permissions. You would have to test this. You should be very careful when deciding to execute code with greator permissions that the user has.

create procedure usp_Something
AS
/*Do Stuff*/
EXECUTE AS dbo
/*Do something*/
REVERT
/*Do more stuff*/
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

I was under the impression that SPs run under the security privileges of the SP owner. So if the SP owner has KILL privileges and the SP attempts to kill a process, it will succeed, even if the SP is instantiated by a user that does not have those privileges.

So I think that you don't need SQL 2005 to do this. Why not test it? I would test it for you but the amount of effort for me to definitively answer the question is the same as for you to answer the question. Just try it!

as dbo:

CREATE SP KillProcess @spid int
AS
... kill code here on @spid
--of course you wouldn't want to do this because it circumvents all security.
GO
GRANT EXEC ON KillProcess TO UserWithoutKillPriv

Now log in as UserWithoutKillPriv and try to kill a process using the SP. Bam, you have your answer.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[mrdenny]

Stored procedures do run under the context of there owner to an extent. As an example you can't truncate a table within a stored procedure unless you have dbo rights. In SQL Server 2005 you can use the EXECUTE AS to increase the rights and run the code block as dbo which is a user who has the rights to truncate the table. The table can now be truncated.

I would assume that the kill command would work along those same lines.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[handbrake]

ESquared - it doesnt appear to work on Win2K. I suspect the reason why is what is says in BOL about the rights for the KILL command "not being transferrable" with required membership of the processadmin or sysadmin roles.

From my testing it appears exactly the same under 2005 as well. Although my attempts to get the EXECUTE AS stuff have failed miserably, I keep getting this sort of error:

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Either I'm screwing up the setup of the users (very likely) or it all just isn't possible.

 

[ESquared]

How about a SQL job that, when it runs, checks a table for a list of spids, which it kills. The job can run entirely under the permissions of dbo. Permission to execute the job should be possible from a stored procedure that a user can run. Or it can execute once a minute--querying an empty table should be a pretty inexpensive.

Just a thought as to how to get around the security issues in 2000 and even 2005.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[mrdenny]

You have to give the application user account the right to impersinate the dbo user.

GRANT IMPERSONATE ON USER::[dbo] TO [ApplicationUser]

This can also be done in the GUI. I can give you instructions if needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[handbrake]

Hi Denny,

I had tried that but must have got the syntax wrong - tried it again and no longer get that error msg I showed above. However I do get the "User does not have permission to use the KILL statement." error, even though inside my dbo.KillProcess sproc I have WITH EXECUTE AS 'dbo'. So perhaps the approach is a non-starter for this feature...

Thanks for all your input on this.

 

[ESquared]

handbrake,

Did you see my suggestion about a SQL job?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[handbrake]

Hi ESquared - yes I did see your latest suggestion thanks. That was going to be next on my list to try when I get a chance... not sure what our DBAs attitude is to sql jobs but as with all of this investigation it's a case of findingany options and then seeing if any are practical to implement...

 

[SQLDenis]

Don't you need certain permissions to create a job? Most likely he won't be able to create the job in the first place

When you go to agent and expand the jobs folder do you see any jobs?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Ask for a job to be created that runs a certain SP once a minute. Make it a harmless SP but come up with some justification for it.

Once the job is in place, modify the SP to check for a list of SPIDs in a table and kill them.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]