SQL server error 15141 triggers while removing a SQL Server domain login on the instance where AlwaysOn is configured.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.
 

What is SQL server error 15141?

SQL Server Error 15141 triggers when the login to be dropped owns an AlwaysON endpoint. We will be able to remove the login from all databases and server roles, however, we will not be able to delete the login because it owns an endpoint. A typical error message looks like:
 

As per the error, one or more endpoints are owned by this login. We can use the below T-SQL code to get the details of endpoint.

SELECT n.name, a.* FROM sys.endpoints a
inner join sys.server_principals n on a.principal_id=n.principal_id
Go

Let us now look into the steps to fix this error message.
 

How to fix SQL server error 15141?

The 15141 error can be fixed by changing the owner of endpoint and then dropping the identified domain login. This breaks the dependency which is the main reason behind the error. We need to change the owner to some other login like the system account sa.

The ALTER Authorization statement can transfer ownership of this endpoint from the existing server-level login to another server-level login. We can not set the ownership to a database-level user. 

Below command can change the owner of the endpoint to a login Domain\test. Please note to replace the login id [Domain\test] with your login.

ALTER Authorization on endpoint::Hadr_endpoint to [Domain\test]
GO

 
After this change, we can drop the login. We can do it by either SQL Server Management Studio or T-SQL. With the SSMS we can perform it by :

1. Connect to target SQL Server Instance.
2. Expand the security folder.
3. Expand the Logins folder.
4. Right-click and choose delete on the identified login which needs to be deleted.
5. Click on the Ok button of the login deletion window.

We can also drop this login with the T-SQL command given below. Please note to change the name of Login_name with your login name which you want to delete.

DROP Login 'LOGIN_NAME'

The login will be dropped successfully this time.

[Need any further assistance in fixing MySQL errors? – We’re available 24*7]
 

Conclusion

In short, SQL server error 15141 triggers while removing a SQL Server domain login. This happens when the login owns an AlwaysON endpoint. Today, we saw how our Support Engineers fix this error.