There are parts of SQL Server where I always like to take expert advice. Sometimes a quick call to a friend can save you tons of time. When I see topics around AlwaysOn, though I know what the basic steps are to building and working with AlwaysOn, the hardcore troubleshooting is not the forte perse. But when I encounter questions around it, I put my learning hat to explore the possible reasons. There was a mail from one of the readers which has been on my Inbox for couple of weeks and I wanted to find the answer. His question goes like this:
While working with AlwaysOn availability group and cleaning it up, I am encountering below error while dropping a login.
DROP LOGIN Pinal
GO
Msg 15173, Level 16, State 1, Line 1
Server principal ‘Pinal’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
Even trying from UI also gives same error
Next task was to find what is causing the error, so I made below query which can tell the exact objects that are dependent and making our DROP command to fail.
SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal')
SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal'))
Based on output, it means there is an endpoint on which Pinal has been granted permission. Second result shows that I have used my account i.e. Pinal to create endpoint and granted permission to AppUser account using below command:
CREATE ENDPOINT [hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO
As the error message suggested, I revoked permission using the below command:
REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO
After the above step, I was able to delete login. Yes, this was a great learning for me today. Do let me know if you ever encountered similar errors in your environments?
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
I ran into the same problem. In my case it maked more sense to just change the owner of the endpoint to sa, for example
alter authorization on endpoint::Hadr_endpoint to sa
After this I was able to drop the login.
I ran into the same problem. In my case it maked more sense to just change the owner of the endpoint to sa, for example
alter authorization on endpoint::Hadr_endpoint to sa
After this I was able to drop the login.
Great. Thanks for sharing.
Please update post accordingly since revoke access may cause issues.
I’m having same problem and I have tried your script to detect the problem and opted to alter authorization to sa instead of revoking because I’m concerned that login will not be able to function as before. And I was able to drop that login. Thanks.
Pinal, great post. Also, good job by ijeb for the ALTER AUTHORIZATION idea. That was all I needed, in my case. I’m thinking this is something to check anytime a new AG is created. I’ve added it to my checklist for Availability Group installation.
Thanks for the comment Mike!
This was the exact situation for,me this one helped to resolve ,Thanks
Hi Pinal – Could you help understand what permissions I need to revoke for this login in order to drop.
class_desc class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 AL ALTER G GRANT
SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 CL CONTROL G GRANT
SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 IM IMPERSONATE G GRANT
public SERVER_ROLE
Hi Pinal ,
I’m getting below error while dropping the Logins
The server principal owns one or more availability group(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)
Need help
Dropping AppUser account should create more problem. Changing ownership of endpoint is real fix.
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;
Thanks, Pinal and also ijeb.
My issue was also the auth to hadr endpoint.
But additionally, I had to alter auth to the AG also.
Thank you, you saved a lot of time and agony … :)