Cannot delete domain login from SQL SERVER 2012

Question

We are in the process of trying to clean up some old accounts on one of our AlwaysOn clusters.

This particular account refuses to play nice and allow itself to be deleted.

USE [master]
GO
DROP LOGIN [PROD\dba007]
GO

Error:

Msg 15173, Level 16, State 1, Line 4
Server principal 'PROD\dba007' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

The permission it's complaining about is a connect permission to the HADR_ENDPOINT

Select perm.* from sys.server_permissions perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = N'PROD\dba007'
class class_desc type permission_name state state_desc
105 ENDPOINT CO CONNECT G GRANT

Now the next logical thing would be to revoke the connect permissions.

USE master;
REVOKE CONNECT ON ENDPOINT ::HADR_endpoint from [PROD\dba007]

But this produces the message

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

The only server role it is a member of is PUBLIC.

How can I find out what exactly is preventing me from revoking these permissions so I can drop the user?

Thanks.

Answer 1

Check to see if they own the endpoint itself:

SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name
FROM sys.database_mirroring_endpoints;

If so, you'll need to change the endpoint owner. Say the endpoint is called Mirroring, and you want to change the owner to SA:

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;