Fix SQL Server Error 15141: The server principal owns one or more endpoint(s) and cannot be dropped
I got Microsoft SQL Server error 15141 while removing a SQL Server domain login on one of instance where AlwaysOn is configured. SQL Server Error 15141 suggests that the login which i want to drop owns AlwaysON endpoints. Here we will fix this issue by changing the owner of endpoint and then will drop the identified domain login.
If you are facing different kind of DROP login failed errors, you can read below articles where i have demonstrated step by step method to fix the various kind of DROP login failed errors:
- How to fix Error 15174: Login owns one or more databases.
- How to fix Error 15173: Revoke the permissions before dropping the login.
- How to fix error 15138 & Error 3729: DROP login failed
Microsoft SQL Server Error 15141
I had to remove a domain login on one of SQL Server instance where AlwaysOn is configured and running. Login was failed to drop because of Microsoft SQL Server error 15141 which suggests that this login owns one or more endpoints. Error details are given below:
Drop failed for Login “Login Name”
The server principal owns one or more endpoint(s) and cannot be dropped. (Microsoft SQL Server, Error:15141)
As error clearly says that one or more endpoints are owned by this login so let us check the details about endpoints. Run 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
You will see the endpoint details in below screenshot along with the login id who owns the endpoint. I have blured it because of privacy.
Solution
As we can see that endpoint is owned by this login so now we will change the owner of this endpoint to break the dependency which are the main reason behind this SQl Server error 15141. Now next step is to change the owner to some another login. You can also transfer the ownership to system account sa.
We will use ALTER Authorization statement to transfer ownership of this endpoint from existing server level login to some another server level login. We can not set the ownership to database level users. ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Run below command to change the owner of above endpoint to a login Domain\abc5.
--Replace the login id [Domain\abc5] with your login. ALTER Authorization on endpoint::Hadr_endpoint to [Domain\abc5] GO
We can see command executed successfully. You can recheck the ownership by executing same command which we have executed in first section. Once change is done. next step is to drop the login.
We can see, now ownership of this endpoint is set to another login. Next step is go ahead and drop the login again. you can do it by either SSMS or T-SQL. SSMS way is given below:
- Connect to target SQL Server Instance.
- Expand the security folder.
- Expand the Logins folder.
- Right click and choose delete on the identified login which needs to be deleted.
- Click on Ok button of the login deletion window.
If you want to drop this login using T-SQL command then open a query window and execute below command.
--change the name of Login_name with your login name which you want to delete. DROP Login 'LOGIN_NAME'
This time your login will be dropped successfully. If you are still facing some another issue during dropping this login, you can read below articles where i have demonstrated step by step method to fix the DROP login failed errors:
- How to fix Error 15174: Login owns one or more databases.
- How to fix Error 15173: Revoke the permissions before dropping the login.
- How to fix error 15138 & Error 3729: DROP login failed
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
Thank you, helped me remove a screwy login and add a fresh one back in.