none
User does not have permission to use kill statement (Please help RRS feed

  • Question

  • Hello,

     

    I really hope someone can help me in this one. I am going crazy for the last 3 or 4 days trying to solve this but its just not working out.

     

    I have a user with the windows Login. He is a part of 10 Active Directory  groups and accessing different databases through them on the server.

     

    he needs to have a permission to use the kill statement to terminate a process.

    i have given him Process Admin on one of the Active directory group on the  server permission for this but he still gets the error

    " User does not have permission to use the KILL statement."

     

    out of furastation I have given him process admin on all the Ad goroup Logins but he still cant kill a statement.

     

    I am using XP_LOGININFO with ALL option to see which AD group the individual belongs to.

    Every time i test the permission with EXECUTE AS option to kill a statement I get the above mentoned error.

     

    I really hope some one can help me at this. what am I doing wrong??

     

     

     

     

     

     

    Friday, September 9, 2011 3:09 PM

Answers

  • That should have worked. To simplify the problem, try bypassing the processadmin fixed server role and AD groups and cut right to the basic ALTER ANY CONNECTION permission.

    GRANT ALTER ANY CONNECTION TO <login>

    If that fails, look for a permission denial somewhere. If that suceedes then most likely the problem is with the AD groups.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, September 13, 2011 2:58 AM
    • Marked as answer by Peja Tao Sunday, September 18, 2011 3:12 PM
    Friday, September 9, 2011 4:04 PM
  • Hi SQLNewbi - you can test this either by loging in as the user account in question and attempting to kill a SPID, or you could create a test login with out the permission added, confirm that the test login cannot kill a SPID, and then grant the permission and try again verifying that the SPID was killed.

     

    I hope this helps.  Please let me know if you have any other questions.

    • Proposed as answer by Peja Tao Tuesday, September 13, 2011 2:58 AM
    • Marked as answer by Peja Tao Sunday, September 18, 2011 3:12 PM
    Saturday, September 10, 2011 6:59 PM

All replies

  • That should have worked. To simplify the problem, try bypassing the processadmin fixed server role and AD groups and cut right to the basic ALTER ANY CONNECTION permission.

    GRANT ALTER ANY CONNECTION TO <login>

    If that fails, look for a permission denial somewhere. If that suceedes then most likely the problem is with the AD groups.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, September 13, 2011 2:58 AM
    • Marked as answer by Peja Tao Sunday, September 18, 2011 3:12 PM
    Friday, September 9, 2011 4:04 PM
  • First, Thank you very much for thehelp.

     

    let me try what you suggested and let see.

     

    i don't think the problem is with AD account because for testing purpose as soon as i make any one of the 10 Ad account sysadmin, than the user can run the Kill statement successfully.

     

    let me try ur advice and let you know.

     

    Thanks

    Friday, September 9, 2011 4:19 PM
  • After doing the Grant alter any connection , how would I test it?

     

    sorry dont know this one.

     

     

    Friday, September 9, 2011 4:24 PM
  • Hi SQLNewbi - you can test this either by loging in as the user account in question and attempting to kill a SPID, or you could create a test login with out the permission added, confirm that the test login cannot kill a SPID, and then grant the permission and try again verifying that the SPID was killed.

     

    I hope this helps.  Please let me know if you have any other questions.

    • Proposed as answer by Peja Tao Tuesday, September 13, 2011 2:58 AM
    • Marked as answer by Peja Tao Sunday, September 18, 2011 3:12 PM
    Saturday, September 10, 2011 6:59 PM