How to Force delete user from Database

There are instances when there are orphan user in the database and it becomes tedious job to delete them. We run below command to drop the user say “dbasdiray_user” from a database called “dbasdiary” and you get below error message:

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.

As per error message, its clear that the user we are deleting has played a role of grantor. Let’s find them by using below commands:

We shall need to find details of grantor using below commands:

use dbasdiary
go
select *
from sys.database_permissions
where grantor_principal_id = user_id (‘dbasdiray_user’);

Basis result what you get from above commands, you would need to perform one of the below command:

REVOKE VIEW DEFINITION ON USER::dbasdiray_user TO public
REVOKE CONTROL ON USER::dbasdiray_user TO public
REVOKE ALTER ON USER::dbasdiray_user TO public
REVOKE ALTER ON USER::dbasdiray_user TO grantee
REVOKE CONTROL ON USER::dbasdiray_user TO grantee
REVOKE VIEW DEFINITION ON USER::dbasdiray_user TO grantee
REVOKE IMPERSONATE ON USER::dbasdiray_user TO ConnectMyApplication AS dbasdiray_user

Above are list of access which could have been granted however there is possibility that some other type of access was granted and you need to change revoke command accordingly.

Please leave your comment if you liked this post or have any feedback.