3

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database trying to generate reports but I only want C to stop and disconnect while A and B are still able to continue with their processes.

  • Don't sure what exactly you mean by "kick out". One thing that you can do is query sys.dm_exec_sessions, kill all queries of this login and disable it, but why don't disable it from the first place? – Michael Cherevko Jan 14 '16 at 8:02
  • A and B are apps normally running to download reports. C is a new app that could be downloading bigger amounts of data depending on what it's doing so we would want to stop it when there's a problem in the server and would like the other 2 apps to be able to continue without problem – Bahamut Jan 14 '16 at 12:27
5

As a one-off you can use EXEC sp_who or EXEC sp_who2 or similar to find the SPIDs of that user's connections and then use KILL <SPID> to stop them. Be aware that any transactions they have open in which changes have been made will need to be rolled back which could potentially take some time and be I/O intensive, but if they are just running reports the effect of this should be minimal. To stop them immediately restarting the processes disable their access before doing this (using ALTER LOGIN <name> DISABLE or by adjusting their access rights more granularly).

If you find yourself needing to do this regularly you could write a procedure to automate the task, so you could just run EXEC kick_user_off '<name>';, though if you need to do this sort of thing regularly enough for it to be worth the effort automating then I suspect you have a process and/or user problem that needs fixing of which this is just a symptom.

| improve this answer | |
  • I had a procedure that took a database name rather than user names, but this approach works best. – Jonathan Fite Jan 14 '16 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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