Identify and change a SQL Server endpoint owner

It is quite common for a DBA or consultant to setup database mirroring or an always on availability group and then leave the organization.
 
When it is time to clean up SQL Server logins on your SQL Server you discover, that although you have revoked access for the login to all databases and server roles, you are still unable to delete the login as it is reported to own an endpoint.
 
Below shows the error reported in SQL Server 2014.
 
image1
 
To confirm the endpoint owner you could use the following:
 

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

 
image2
 
To change the endpoint owner to sa for an endpoint called Mirroring you would use the following:
 

USE master;
ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;

 
image3
 
Confirm that the endpoint is now owned by sa:
 
image4
 
The legacy DBA or consultant’s login can now be removed from the SQL Server.

5 Comments

Peter

You need to also grant connect on the endpoint or your databases will not synchronize after a fail over. Note this does not need to be done if you use the service account.

GRANT CONNECT ON ENDPOINT::Mirroring TO [sa]

kevin

Thanks Peter, yes once the endpoint owner has been changed it is important to ensure that the remote SQL Server instance can still connect to the endpoint. Generally this is done by granting connect to the remote SQL Server service account i.e.

GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\remote_sql_server_service_account_name];

Hany

@Peter
GRANT CONNECT ON ENDPOINT::DBMirroring TO [sa]

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

kevin

You can check the sa permissions on an endpoint (below shows for a endpoint called Mirroring) by running the following:

EXECUTE AS LOGIN = 'sa';
SELECT * FROM fn_my_permissions('Mirroring', 'ENDPOINT');
REVERT;
GO

If you see CONNECT under permission_name for the entity_name Mirroring then the login has the connect permission.

You should find that sa has the connect permission so you don’t have to grant it.

Leave a Reply