Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Kill Users Without Processadmin Role

    Hi folks, i create a procedure in master db that kill users spids. Giving EXECUTE permission on the procedure to users without giving processadmin role doesn't work! Any guidelines?



    Howdy!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Other than "Don't do that", nope.

    Killing processes requires a lot more understanding of the server than the average user is ever likely to have. Our tech support people always want that ability, but I've never found any remotely safe way to give it to them because they don't think in terms of the impact that killing a process might have.

    -PatP

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Teacher, it's the same LOCKING issue; The WELL DESIGNED VB APPLICATION timesout often coz of a table exclusively locked by one of the user; if the DBA isn't available; what to do then. This is not the solution but at least it would help the users to proceed.
    No doubt, It's coz of the poorly designed tables; but i can't change all just once!


    Howdy!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll conceed that this is drastic, but if you have to give them something to fix the problem without having someone technically competent (a dba) to resolve the underlying problems on duty, then the best answer I've found is to give two people permission to reboot the database server.

    If the entire machine (therefore the SQL Server) goes down, all of the database connections will be dropped at once. The resulting rollbacks will be handled by the recovery process when the SQL Service restarts after the reboot.

    The total impact of a reboot will be large, but it will still be smaller than the probable impact of killing arbitrary spids. This isn't a good solution, but it is the best one I can offer if you can't have a dba that understands the issues on hand to resolve these problems.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •