none
Unable to give a user Control permission on availability group in SQL 2012 Enterprise SP4-OD RRS feed

  • Question

  • Hi, 

    I am tyring to give a login to contorl availabilty group permission, so he can failover it. But i am getting below error. I have tried giving user SA permission but still the same error. Try restarting servers, faiging over but it is not working. But in SQL 2016 it work right away. not sure is it a proble with SQL Version. Anybody can advise on this please. 

    SQL version: SQL 2012 Enterprise SP4-OD

    Error:

    Cannot continue the execution because the session in the kill state

    A severe error occurred on the current command.  The results, if any, should be discarded.

    Wednesday, February 20, 2019 3:50 AM

All replies

  • You get this error when performing any of the below?
    1) Creating login?
    2) New login with SA privelege you created tries to failover AG?

    I think AG is in transition state. Can you check if the failover has actually completed and then try?


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Wednesday, February 20, 2019 4:49 AM
  • Hi dandiwal2,

     

    Please refer to the similar thread.

    https://social.technet.microsoft.com/Forums/en-US/94ae0aaf-b2de-4354-8456-5f5b6e139026/cannot-continue-the-execution-because-the-session-in-the-kill-state?forum=sqlkjmanageability

     

    Steckle: In my case the root cause for this was the fact that the underlying availability group (which contained the database the job was working on) performed a failover - hence, local activities on the database were terminated.

     

    You can also refer to the old thread: https://www.sqlservercentral.com/Forums/1809734/CHECKDB--Cannot-continue-the-execution-because-the-session-is-in-the-kill-state

     

    This error message is caused by your HAG (High Availability Group) SQL Server failing over from one node to the other.

    If your HAG is in Synchronous-commit mode log entries cannot be committed on the primary node until they have been written to the secondary nodes. If the heartbeat between the machines fails, and the server attempts to fail over it has to ensure that both nodes are in a consistent state. The databases change from being Synchronized to Resolving mode, and any transactions on the primary node that have not been committed to the secondary nodes are rolled back. This rollback is what gives you the error message, as the automatic rollback state is the same as if you had issued a kill command against the SPID.

    The cause is usually network related, especially if the nodes are geographically separated, as will be the case with a DR copy at a remote site. If they are not then check you local NICs and switches.

    The recommended resolution from Microsoft is to increase the LeaseTimeout and/or HealthChecktimeout values.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 20, 2019 8:14 AM
  • no creating login is fine, also giving SA permission is all fine. no issues in that. but when i run below command it give me the error 

    USE master;  
    GRANT CONTROL ON AVAILABILITY GROUP::Availabilitygroup TO user;  
    GO  

    Wednesday, February 20, 2019 8:09 PM
  • no i confimred the always on in consistent state(synchronized)
    Wednesday, February 20, 2019 8:15 PM
  • Your newly created login with SA privilege should be able to failover and failback AG without issues. Make sure the newly created login is present on both primary and secondary.

    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Wednesday, February 20, 2019 10:33 PM
  • thanks Vivek, i know that but i don't want to give this user SA privilege, the user is not a DBA so avoiding giving high privilege. just control permission on always on for failing over during OS patching via running powershell script. 
    Thursday, February 21, 2019 12:30 AM
  • Hi Emily,

     So you mean that because some uncommitted transactions caused this error when i try to give a user control permission. And by increasing timeout between replicas would help in fixing this issue. Also, i don't recieve .Net recieve which comes up during failing over. My databases are in synchronized state always. 

    Thank you 

    Jaspreet


    • Edited by dandiwal2 Thursday, February 21, 2019 12:34 AM forget to add some more info
    Thursday, February 21, 2019 12:32 AM
  • no creating login is fine, also giving SA permission is all fine. no issues in that. but when i run below command it give me the error 

    USE master;  
    GRANT CONTROL ON AVAILABILITY GROUP::Availabilitygroup TO user;  
    GO  

    The fact that it is erroring out just when the above statement is run is strange. It should work in SQL 2012 so it's not version related. Do you have any triggers etc that are preventing it? Although unlikely but can you try using a lower version of SSMS? Have you tried running it on all replicas?

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, February 21, 2019 1:48 PM
  • Hi Mohsin,

     Yes i tried running on all replicas. no way i cannot use lower version as it is quite secure environment and lots of work would be involved for installing lower version of SSMS as it is prod , so i don't think so i will get approval.Also, checked no trigger/sp preventing it.


    • Edited by dandiwal2 Friday, February 22, 2019 12:50 AM forget to answer
    Friday, February 22, 2019 12:40 AM
  • I just tried it in on one of my SQL Server 2012 lab machine and it worked well.
    Can you try executing it from command prompt using sqlcmd and see what happens?


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Proposed as answer by Abbottee Monday, February 25, 2019 10:01 AM
    Friday, February 22, 2019 12:47 AM
  • Same error message
    Monday, November 18, 2019 8:45 PM