The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, March 18, 2024 Login
Public

Server principal 'some user' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal. 12/8/2019 10:23:23 AM

https://blog.sqlauthority.com/2015/07/21/sql-server-fix-server-principal-login-name-has-granted-one-or-more-permissions-revoke-the-permissions-before-dropping-the-server-principal/

this will then lead you too:

https://www.mssqltips.com/sqlservertip/5201/drop-login-issues-for-logins-tied-to-sql-server-availability-groups/

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

Based on output, it means there is an endpoint on which Pinal has been granted permission. Second result shows that I have used my account i.e. Pinal to create endpoint and granted permission to AppUser account using below 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)
GO
GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

As the error message suggested, I revoked permission using the below command:

REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

Definitely the best fix if related to always-on is change endpoint to service account or sa

alter authorization on endpoint::Hadr_endpoint to sa

Let's get a list of Availability Groups owned by the login:

USE [master]
GO
SELECT ag.[name] AS AG_name, ag.group_id, r.replica_id, r.owner_sid, p.[name] as owner_name 
FROM sys.availability_groups ag 
   JOIN sys.availability_replicas r ON ag.group_id = r.group_id
   JOIN sys.server_principals p ON r.owner_sid = p.[sid]
WHERE p.[name] = 'DOMAIN\DBAUser1'
GO   

Now we will re-assign the AG's ownership to another login (preferably service account. 

USE [master]
GO
ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLAG1 TO [DOMAIN\DBAUser1];
GO

Blog Home