How do I force the delete of users from my database?

Question

I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is I am unable to delete any of them.

When I run a command like below:

DROP USER [MyGarbageUser]

It prompts me following error:

Msg 15284, Level 16, State 1, Line 5 The database principal has granted or denied permissions to objects in the database and cannot be dropped.

These users neither own any objects in this database(as far as I know) nor they should. After a ton of searching on the net I have still not been able to find solution.

How can I force MyGarbageUser to be deleted from this database?

Answer 1

It seems the user that you are trying to drop has played a role of grantor and hence you need to revoke those accesses before performing drop of user.

Try running below command to get details of grantee first:

select                     
     permission_name,                     
     state_desc,                     
     object_name(major_id) as securable,                     
     user_name(grantor_principal_id) as grantor                     
from sys.database_permissions                     
where grantee_principal_id = user_id('User Name to be dropped')    

Next will be to find details on grantor using below:

select *                     
from sys.database_permissions                     
where grantor_principal_id = user_id ('User Name to be dropped');     

Basis result what you get from above query, you need to perform one of 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*     

This is just a possibility, there could be some other type of access which has been granted. so, you need to check the same and execute revoke accordingly.

You may find more of these examples from below forum:

https://ask.sqlservercentral.com/questions/24721/the-database-principal-has-granted-or-denied-permi.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/07b40291-6c4d-427e-a5e2-568df482c550/drop-user-fails-with-quotthe-database-principal-has-granted-or-denied-permissions-to-catalog?forum=sqlsecurity

https://stackoverflow.com/questions/41817820/how-to-solve-sql-server-error-15284-the-database-principal-has-granted

Hope above helps.