Have you ever tried to drop a user from SQL in an effort to clean up your security and gotten the error: Server principal ” has granted one or more permissions(s).?
Was it related to SQL Server 2012 and your Availability Groups?
I ran into this recently and if it helps someone, then good!
First, make sure you know what the permission was for:
Select * from sys.server_permissions
where grantor_principal_id =
(Select principal_id from sys.server_principals where name = N’userNameHere’)
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
105 ENDPOINT 65536 0 283 282 CO CONNECT G GRANT
You should see an entry [or more than one] and the grantor would have the id of the user you were trying to remove — in this case it is for an endpoint. Now find out what that actual permission is for:
Select * from sys.server_principals where principal_id = 283 [replace with ID]
You can see that when creating the Availability Group the user creating the AG was assigned as giving permission to the HADR_Endpoint to the cluster account.
If you use the UI to change the permission it won’t work. You have to actually issue the t-sql:
ALTER Authorization on Endpoint::[Hadr_endpoint] to [OtherUser]
Now that this is done, you should be able to disable the existing user [or deny access] until you are certain it isn’t being used — and then feel free to delete it!
Questions? Comments? Post!