As a SQL Server user, there are times when we encounter errors or face challenges that require expert advice. One such area is working with AlwaysOn availability groups. While we may know the basic steps involved in building and managing AlwaysOn, troubleshooting complex issues can be a daunting task. In this blog post, we will explore a common error that occurs when trying to drop a login in an AlwaysOn availability group and discuss the steps to resolve it.

A reader recently reached out to me with the following question:

“While working with AlwaysOn availability group and cleaning it up, I am encountering the following error while trying to drop a login:

DROP LOGIN Pinal
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.”

This error indicates that the login ‘Pinal’ has granted permissions to one or more objects, preventing it from being dropped. To identify the exact objects causing the error, we can use the following query:

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'
    )
)

The first query retrieves the objects for which the login ‘Pinal’ has granted permissions, while the second query lists the logins that have been granted permissions by ‘Pinal’.

Based on the output of these queries, we can determine that there is an endpoint on which the login ‘Pinal’ has been granted permission. This means that the login ‘Pinal’ has created an endpoint and granted permission to another account, ‘AppUser’, using the following 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)

GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser

To resolve the error, we need to revoke the permission granted to ‘AppUser’ on the endpoint. This can be done using the following command:

REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser

After revoking the permission, we can successfully delete the login ‘Pinal’ without encountering any errors.

Understanding SQL Server permissions and troubleshooting common errors like this can greatly enhance our skills as database administrators. If you have encountered similar errors in your environments, I would love to hear about your experiences and how you resolved them.

Stay tuned for more SQL Server tips and tricks!