SQL Login User Dropped Failed

2014-10-13

Situation:

The User1 is no longer in the company and only "Public" server role. When I type command:
"Drop Login [xxx\User1]", it showed the errors below
Login 'xxx\User1' has granted one or more permission(s). Revoke the permission(s) before dropping the login.

 Solution:

1. Check User1 has still been login the server
 sp_helplogins 'xxx\User1';
(Result:No)

2. Check DB Level permission
--DB level
SELECT permission_name, state_desc, OBJECT_NAME(major_id) as securable
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('xxx\User1');
(Result:No)

3. So User1 didn't own permission on DB level, also didn't own Objects. Maybe, User1 granted permission to other Server Principals.. Let's try it
Select sper.major_id as [Benefit_User_ID], SUSER_NAME(sper.major_id) as [Benefit_User_Name],
        sper.grantee_principal_id as [affected_limited_user_ID], SUSER_NAME(sper.grantee_principal_id) as [affected_limited_user_Name] ,
        sper.state_desc as [Did_What], sper.Permission_name as [What_Objects]
    from sys.server_permissions sper
    JOIN sys.server_principals sprin
    on sper.grantee_principal_id = sprin.principal_id
    ORDER by sprin.[name];

BINGO! we can see these columns
<class, class_desc, major_id, grantee_id, grantor_id, Type, Permission_name, state, state_desc>
You need to "REVOKE" these permissions which are granted/DENY/XXXX by "User1", then you can DROP LOGIN


Newer Older