SQL Server: Change Availability Group Endpoint Ownership

I’m doing some cleaning on my customer’s instances.
I want to delete the login of a previous DBA for 2 reasons; this person does not work anymore in my customer’s company and all DBA are members of a group that is given permission on the instances. I don’t want to see any DBA’s personal login on SQL Server instances.
When I try to delete the login I receive the following error;

Msg 15173, Level 16, State 1, Line 4
Server principal 'MyDomain\AccountName' has granted one or more permission(s). 
Revoke the permission(s) before dropping the server principal.

There are no permissions set at the instance level or at the database level for this login.
I get this error because the login is the owner of the Database Mirroring Endpoint.

Every object on SQL Server has an owner. The endpoint got the ownership back when the AlwaysOn Availability group was created by the DBA.
The following query will show the owner of the Database Mirroring endpoint. Running this query on dozens of instances shows several personal DBA accounts as the owner of the Database Mirroring endpoint.

select name, SUSER_NAME(principal_id) AS OwnerName, type_desc
from sys.endpoints
where name = 'Hadr_endpoint'

As described in the documentation the principal_id here is the “ID of the server principal that created and owns this endpoint”.
The Endpoint owner can be changed using the ALTER AUTHORIZATION command like this;

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;

Before doing so, as mentioned in the error message, we need to careful about the permissions granted by the account we want to remove.
The endpoint owner granted the CONNECT permission on the endpoint to the SQL Server service account.
Doing ALTER AUTHORIZATION will drop this permission which will disconnect your AlwaysOn replicas.

We can verify the CONNECT permission using the following query.

select e.name
	, p.state_desc, SUSER_NAME(p.grantor_principal_id) AS Grantor
	, p.permission_name, SUSER_NAME(p.grantee_principal_id) AS Grantee
from sys.endpoints AS e
	join sys.server_permissions AS p
		on e.endpoint_id = p.major_id
where name = 'Hadr_endpoint'

The DBA login is indeed the grantor of the CONNECT permission to the endpoint for the service account.
After running the ALTER AUTHORIZATION command we need to grand again the permission.

Here are the commands to change the ownership on the AlwaysOn endpoint and grant CONNECT back to the service account:

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [MyDomain\svc_sql]

Now, running the 2 previous queries gives the following result.

I can now delete the DBA login from the instance.
In my opinion, changing the AlwaysOn endpoint owner to the “sa” login could be a best practice to apply after setting up a new Availability group.

PS: An other error linked to AlwaysOn that you can face when dropping a login is the following.

The server principal owns one or more availability group(s) and cannot be dropped.

This one is about the owner of the availability group which can be identified and modified with the following queries.

select g.name AS GroupName, p.name AS OwnerName
from sys.availability_groups as g
	join sys.availability_replicas AS r
		on g.group_id = r.group_id
	join sys.server_principals AS p
		on r.owner_sid = p.sid

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[MyAGName] TO sa;