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:
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: