All we need is an easy explanation of the problem, so here it is.
I want to delete a server principal from my server because the member is not working at our company anymore. Unfortunately this principal is owner of objects / grantor of permissions because he configured the availability group and so on.
I found this page which describes a way to get rid of all the permissions / grant them again through sa link and it worked out great so far. Unfortunately there is one permission that has been granted by the principal that i can’t find anywhere.
The query that I use go get the granted permissions by this principal:
USE [master] GO SELECT pm.class, pm.class_desc, pm.major_id, pm.minor_id, pm.grantee_principal_id, pm.grantor_principal_id, pm.[type], pm.[permission_name], pm.[state],pm.state_desc, pr.[name] AS [owner], gr.[name] AS grantee FROM sys.server_permissions pm JOIN sys.server_principals pr ON pm.grantor_principal_id = pr.principal_id JOIN sys.server_principals gr ON pm.grantee_principal_id = gr.principal_id WHERE pr.[name] = N'PRINCIPALNAMEHERE';
But i don’t know where the above major_id references to. It is not in sys.endpoints or sys.services. Also the rights given to the availability group that this server is in (there is only on) don’t refer to the same major_id.
I tried googling thing like ‘sql server major_id "65537"’ and ‘sql server permissions major_id "65537"’ but I kept stumbling on things that don’t apply to my situation.
So now I call for help here, hopefully finding someone who has more experience in this situation or someone with the golden ticket.
Thanks in advance!
It looks like the major_id is refering to the replica_metadata_id in the sys.availability_replicas DMV. When i look at new granted permissions on the same class_desc as in the above query output screenshot it corresponds to be the same:
Am I onto something here?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I was able to solve this with the microsoft support team. When we delete the grantee server principal (so not the grantor, but the one who is granted the rights).
After deleting the grantee server principal the granted rights disappeared. After that i could delete the grantor server principal.
Add the deleted grantee principal back and assign the same right, and done!
Glad to hear that you were able to solve this with support from Microsoft. You could also run below query to check the grantor by running below command:
select * from sys.database_permissions where grantor_principal_id = user_id ('User Name to be dropped');
Based on result you get from above command, you shall need to execute on the command as per below:
REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO public REVOKE CONTROL ON USER::User Name to be dropped TO public REVOKE ALTER ON USER::User Name to be dropped TO public REVOKE ALTER ON USER::User Name to be dropped TO *grantee* REVOKE CONTROL ON USER::User Name to be dropped TO *grantee* REVOKE VIEW DEFINITION ON USER::User Name to be dropped TO *grantee*
Above commands are just possibility, there could be some other type of access which has been granted. so, you need to check the same and execute revoke accordingly.
I have covered this on my post:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂