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!
Sunday, April 11, 2021 Login
Public

Blog posts for the month of December,2019.
Seeing Double12/10/2019 2:45:42 PM

Seeing Double.

Flash back to Sixteen Candles.

 

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