My colleague was dealing with a request from another member of IT today – they’re currently automating the process of patching, and as such some permissions were needed for the service account dealing with the automation jobs.
Specifically they needed control of the Availability Group object – so my colleague ran the following code
GRANT CONTROL ON AVAILABILITY GROUP::[AG_Name] TO [Login_Name];
To the above we received the following error message
The statement has been terminated.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Uh oh. That doesn’t look healthy. In addition to the above error we found a nice stack dump had been generated.
SELECT ar.replica_server_name ,ag.name AS ag_name ,ar.owner_sid ,sp.name FROM sys.availability_replicas ar LEFT JOIN sys.server_principals sp ON sp.sid = ar.owner_sid INNER JOIN sys.availability_groups ag ON ag.group_id = ar.group_id WHERE ar.replica_server_name = SERVERPROPERTY('ServerName') ;
Low and behold, we had a NULL owner_sid column. Somehow the owner of the AG object itself had dropped out. How this happened, I’m still not sure – I cannot drop logins that own an availability group.
The fix is nice and simple
ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AG_Name] TO [sa];
The above will set the owner of the availability group object, at which point you can rerun the original script just fine!
This seems to have been reported to Microsoft a couple of times, but to date no fix has been issued.