Today we are going to discuss about how to resolve the “Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped”
Introduction
If you are a SQL Server DBA, you may encounter the Error 15141 when trying to delete a login id. First of all let me show you how the error message looks like in SSMS when you try to delete/drop a login.
In the above example screen shot we are trying to delete the login id ‘Admin’. I have observed that some DBA(s) do not read the error message carefully and starts beating about the bush.
There are very similar error messages while dropping logins as you can refer the following links:
Error 15141: The Server Principal Owns One or More Availability Groups
Error 15141: The Server Principal Owns One or More Endpoints
Error 15434: Could not drop login as the user is currently logged in
To emphasize if you observe the above error message clearly reads that the principal (or login) owns one or more server roles, which prevents you from dropping it.
Cause Of the Error 15141: The Server Principal Owns One or More Server Roles
When a server principal or login owns a server role, you cannot drop the login unless you first transfer ownership of the server role to another login. As a matter of fact SQL Server never allows you to drop a login if it owns any object. Hence it throws the error preventing you to drop the server principal or login.
Resolution
To resolve the error, you first need to identify the Server Roles owned by the login or server principal. Then you need to transfer the ownership for each of the server roles to ‘sa’ or any other login as per your organization standard.
1. Query to Identify the Server Roles the Login owns
SELECT sp1.name AS ServerRoleName, sp2.name AS RoleOwnerName FROM sys.server_principals AS sp1 JOIN sys.server_principals As sp2 ON sp1.owning_principal_id=sp2.principal_id WHERE sp2.name='Admin' --Change the login name
Sample Output:
Here in the above example it shows that the login id ‘Admin’ owns two Server roles. On the contrary if the login would have own one or more database role(s), it would allow to delete the login but not the user. Now we’ll change the ownership.
2. Query to Change the Server Role Owner:
USE [master] GO ALTER AUTHORIZATION ON SERVER ROLE :: [ServerRole-Test] TO [sa] --Change The ServerRole Name and login Name GO ALTER AUTHORIZATION ON SERVER ROLE :: [AnotherServerRole-Test] TO [sa] --Change The ServerRole Name and login Name GO
3. Drop the Login:
USE [master] GO DROP Login [Admin] --change the login Name
Query Result:
As can be seen now the the drop command completed successfully.
Conclusion:
To summarize the error Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped occurs when the login or server principal owns one or more Server Roles and you are trying to drop the login. Obviously you should not take any knee jerk reaction. After all you are trying to resolve the issue. Hence you should first list out the Server Roles which the login owns. Then change the ownership to [sa] or any other login as per your organization standards. Finally delete or drop the login. Definitely this should resolve the issue. Important to realize that this method will allow you to delete the login even if the login owns and database roles. Hence to repeat you need to be very careful while working in production systems.
