CODEWITHSUNDEEP
sql-server-2008
mnDBA
mnDBA

Reputation: 139

Application using KILL command to terminate DB Connection

All,

I have made up my mind that this is not going to happen, but often being one of just a couple of DBAs in my shop I like to hear the experiences of others.

We have a vendor stating they require processadmin role membership so their app can KILL long running or user cancelled queries against their DB. In 10+ years I have never ran into this as a requirement before. We currently house 400+ databases and none have users in the processadmin role. In my mind, even if the user cancels a given process within the app, the application code is responsible for gracefully opening and closing the connections as needed. If a query is taking "too long" it needs to be tuned.

Depending on what is being KILLed, rollback could be quite burdensome and cause blocking. I would also be concerned they could easily kill the wrong connection in a shared environment.
Anyone else run into a situation like this? Anything obvious I am missing?

Upvotes: 4

Views: 254

Answers (3)

Ben Thul
Ben Thul

Reputation: 32707

While processadmin will get them what they need, you could have them create (or create one on their behalf) a signed module that only has the ability to kill certain processes (i.e. ones from their app). But even then, I'll resonate what everyone else is saying; this is not a good general practice.

Upvotes: 0

TroubleshootingSQL
TroubleshootingSQL

Reputation: 71

If you are relying on KILL for handling long running queries which shouldn't be long running, then the T-SQL code definitely needs tuning. If these are ad-hoc SQL being sent to the engine from an on-the-fly query designer based on what the users select as their preferences from the application/web page, then the options being provided to the user needs to be looked at. I have never been an advocate of killing queries unless and until there is no other option. To resolve a production related outage (as a workaround or immediate solution), it might be acceptable at times but not as a practice.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96570

I would not buy an application from a vendor like that, it is bound to be badly performing, badly designed and buggy. Anyone who is incompetent enough not to know that applications should not have admin rights is not someone I would buy a product from.

Upvotes: 2

Related Questions