It’s a very common issue which every DBA faces in there daily administrative activities. When we restore a database along with it few users comes along, which own’s permission on database objects.  These user’s are orphaned which need to be deleted.

While deleting these user we faces a very common error

“DROP LOGIN FAILED FOR USER ‘XYZ'”.

The database principal has granted or denied permissions to objects in the database and cannot be dropped.

To resolve these kind of  issue we struggles a lot and spend our precious time in these type of activities.

Here we will see how to over  come these issues without spending much time.

Once the database is restored check for Orphaned Users

USE <Databasename>;
GO
sp_change_users_login @Action=’Report’;
GO

Once Users are identified we will figure out what permission they hold in the database

1).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(‘XYZ’)

2.)select * from sys.database_permissions

where grantor_principal_id = user_id (‘XYZ’);

 

Once user permission are identified revoke the permission.

REVOKE the defined permission from step1.

REVOKE the impersonate permission from user ‘XYZ’ .

What is Impersonation?

SQL Server supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement, or implicitly by using the EXECUTE AS clause on modules. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

Implicit impersonations that are performed through the EXECUTE AS clause on modules impersonate the specified user or login at the database or server level. This impersonation depends on whether the module is a database-level module, such as a stored procedure or function, or a server-level module, such as a server-level trigger.

REVOKE IMPERSONATE ON USER::labelsecurity TO public

The above statement will execute successfully, but still not able to drop the user same error as mentioned above.

Execute the Query in step 2 , get the granter_principal_id then pass the same in below query to get the user name who impersonated the user.

SELECT USER_NAME(granter_principal_id)

use [Database_Name]
GO
REVOKE IMPERSONATE ON USER::[XYZ] TO [RU] AS [XYZ]
GO

Now try dropping user again.