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
No comments
Post a Comment