debugsqlMinor
Cannot delete domain login from SQL SERVER 2012
Viewed 0 times
cannot2012deletesqlloginserverfromdomain
Problem
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.
Error:
The permission it's complaining about is a connect permission to the HADR_ENDPOINT
Now the next logical thing would be to revoke the connect permissions.
But this produces the message
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.
This particular account refuses to play nice and allow itself to be deleted.
USE [master]
GO
DROP LOGIN [PROD\dba007]
GOError:
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 GRANTNow 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.
Solution
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:
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;Code Snippets
SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name
FROM sys.database_mirroring_endpoints;ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;Context
StackExchange Database Administrators Q#166140, answer score: 8
Revisions (0)
No revisions yet.