Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

It is very common for a DBA to set up AlwaysOn in a SQL environment and whoever creates the Availability Group is, by default, the owner of the endpoint. If employee who has created AAG and left the company in this case you need to delete login for him\her for SQL related and when go and try to drop ex-employee login we will not be able to delete the login because it would be the owner of an endpoint since he\she might have setup it.

Though employee leave the organization still AAG and all will work fine it wont affect anything unless management asked you to delete ex-employee login and once you go and try to drop it you may receive below error message.

“The server principal owns one or more endpoint(s) and cannot be dropped (Microsoft SQL Server, Error: 15141)”.

To check and see who the owner of your endpoints are:

USE master
GO
SELECT e.name as EndpointName,
sp.name AS EndpointOwner,
et.PayloadType,
e.state_desc
FROM sys.endpoints e
INNER JOIN sys.server_principals sp
ON e.principal_id = sp.principal_id
RIGHT OUTER JOIN ( VALUES ( 2, 'TSQL'),
( 3, 'SERVICE_BROKER'), ( 4, 'DATABASE_MIRRORING') )
AS et ( typeid, PayloadType )
ON et.typeid = e.type

Note: The AlwaysOn endpoint will have the name Hadr_endpoint and will have a DATABASE_MIRRORING payload.

Run the following statement to make the change:

USE master
GO
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa

If you are creating an AlwaysOn Availablitiy Group and want to use T-SQL statements instead of the wizard, you have the ability to specify the endpoint owner

CREATE ENDPOINT endpoint_mirroring  
AUTHORIZATION <loginname>
STATE = STARTED   
AS TCP (LISTENER_PORT = 5022)  
FOR DATABASE_MIRRORING (  
   AUTHENTICATION = WINDOWS KERBEROS,  
   ENCRYPTION = SUPPORTED,  
   ROLE=ALL);  
GO

In the statement above, if AUTHORIZATION is not specified with a SQL or Windows login, the caller will become the owner of the newly created endpoint. To use AUTHORIZATION and assign ownership to a login, the caller must have IMPERSONATE permission on the specified login.