Application using KILL command to terminate DB Connection

Total Post:28

 963  View(s)
Rate this:

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?

  1. Post:397

    Re: Application using KILL command to terminate DB Connection

    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.

    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.

      Modified On Apr-10-2018 12:31:07 AM