SQL Server 2012 – Cannot Delete Domain Login

permissionsSecuritysql serversql-server-2012

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.

Best Answer

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;