Error 15141: The Server Principal Owns One or More Endpoints and Cannot be Dropped

Problem:

As a DBA you may encounter the following error message “The Server Principal Owns One or More Endpoints and Cannot be Dropped. (Microsoft SQL Server Error:15141)” while deleting a login id in Always On SQL server.

This happens because the user (DBA) configured the Always on and configured it. As security best practice we should not keep any individual login id in SQL Instances. All domain logins should be part of the Active Directory group.

The error message:

The Server Principal Owns One or More Endpoints and Cannot be Dropped

What is Endpoint in SQL Server:

SQL Server Endpoint is a communication channel that enables clients to connect to the server and access data. It is a database object that defines the ways and means in which SQL Server may communicate over the network. In SQL Server Always on this endpoint is a special-purpose endpoint that is used exclusively to receive connections from other server instances.

SQL Server will not allow you to drop a user that owns an endpoint because it would disrupt communication for the clients that use the endpoint.

Resolution:

As shown above, the error clearly says that the login owns one or more endpoints. First thing is NOT to have a knee jerk reaction to delete the endpoint itself.

First you need to check the endpoints and their ownership. Second you can change the ownership to “sa” or the domain account as per your organization standards.

Query to to check the Endpoint Ownership:

USE master
GO 
SELECT e.name as  EndpointName,
sp.name AS  EndpointOwner,e.type_desc AS Endpint_Type, e.state_desc  AS Endpoint_Status
FROM  sys.endpoints e
INNER JOIN  sys.server_principalssp
 ON e.principal_id = sp.principal_id

Output Showing individual login ID owning the Endpoint “Hadr_endpoint”:

Error 15141: The Server Principal Owns One or More Endpoints and Cannot be Dropped

Furthermore the endpoint’s name is “Hadr_endpoint” which is the default name of the Availability Group (AG) endpoint. Hence this confirms the individual loginID is part of Always On setup .

Next step is to change the ownership of the endpoint to the login id as per your organization’s standards. In this example I am changing it to “sa”

Here we are using ALTER Authorization statement to transfer ownership. We can not set the ownership to database level users. ALTER AUTHORIZATION is the statement to change the ownership of any entity that has an owner.

Command to change the Endpoint Ownership:

--Replace the login id [sa] with your login.
ALTER Authorization on endpoint ::Hadr_endpoint to [sa]
GO

Now again run the Select query to check the Ownership. Once confirmed you can try dropping the loginid.

Drop LoginID:

DROP Login  [GlOBAL\Tridib.Dev] --Change the loginID you want to delete

Error 15141: The Server Principal Owns One or More Endpoints and Cannot be Dropped

Conclusion:

In summary, By understanding endpoint and why dropping a user with an endpoint is prohibited, you can quickly resolve this issue by transferring the ownership to a standard account and continue with your database administration tasks.

You can refer Error 154934: Could not drop login as the user is currently logged in

Error 15141: The Server Principal Owns One or More Availability Groups

Please spread the word:

https://sqlgeekspro.com/the-server-principal-owns-endpoints-and-cannot-be-dropped/