EXCEPTION_ACCESS_VIOLATION when working with Availability Groups – George Palacios

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.

This at first struck me as a bug that would need to be raised to Microsoft. After a conversation with my good friend Peter Vandivier on DBA.StackExchange

some brain cells rubbed together and I remembered an old blog post I’d read about Owners of the Availability Group objects. As a bit of a punt I ran the following script to check for the AG object owners.

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.

Leave a Reply Cancel reply