How To Change the Owner of Availability Group Endpoints

By default when Availability Group endpoints are created the owner is set as the login of the account that created it.

It is a good idea to change this to the sa account. DBA’s come and go and you don’t want your endpoints owned by an old DBA login.

It also means you can’t remove the DBA’s login from the server until you do change any owned objects like databases or endpoints.

Be careful though. The reason you need to take care with this is that when you alter it to sa, the connect permissions that were previously granted drop off.

This may or may not cause a problem depending on whether or not the account used on the SQL Service is a sysadmin on the server you have the endpoint on.

To do this without the possibility of causing any impact the best way is to alter and grant the permissions all in one update.

How to check the current owner

USE master; 

SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name 
FROM sys.database_mirroring_endpoints; 

--Check and record any permissions. (you will need to add this back) 
SELECT EPS.name, SPS.STATE, CONVERT(nvarchar(38), SUSER_NAME(SPS.grantor_principal_id))AS [GRANTED BY], SPS.TYPE AS PERMISSION, CONVERT(nvarchar(46),SUSER_NAME(SPS.grantee_principal_id))AS [GRANTED TO] 
FROM sys.server_permissions SPS , sys.endpoints EPS WHERE SPS.major_id = EPS.endpoint_id AND name = 'Hadr_endpoint' 
ORDER BY Permission,[GRANTED BY], [GRANTED TO]; 

Now run your batch.

BEGIN TRAN USE master; 

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa; 

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [yourdomain\username]; 

COMMIT 

Make sure you do this out of hours, or within an outage window, just in case something goes wrong.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply