Server principal LOGIN has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

See more:

SQL

SQL-Server

SQL-Server-2014

, +

I am trying to delete a login in SQL Server using the command

<pre lang="SQL">DROP LOGIN [user]

BUt I get the following error message:

<pre>Server principal LOGIN has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.</pre>

I have been reading several forums and was able to obtain the query below to get the privilege of the login I was trying to drop and the users it granted with access.

DECLARE @loginname nvarchar(100)
SET @loginname='AP\asa.c'

SELECT dp.type_desc, dp.SID, dp.name AS user_name  
FROM sys.database_principals AS dp  
LEFT JOIN sys.server_principals AS sp  
    ON dp.SID = sp.SID  
WHERE sp.SID IS NULL  
    AND authentication_type_desc = 'INSTANCE';  

	SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = @loginname)

SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = @loginname))

I opened articles using the same query but they all pertain to ENDPOINT however, my case returns different.

I have the below result.

class_desc	class	class_desc	major_id	minor_id	grantee_principal_id	grantor_principal_id	type	permission_name	state	state_desc
SERVER_PRINCIPAL	101	SERVER_PRINCIPAL	354	0	2	354	AL  	ALTER	W	GRANT_WITH_GRANT_OPTION
SERVER_PRINCIPAL	101	SERVER_PRINCIPAL	354	0	2	354	CL  	CONTROL	W	GRANT_WITH_GRANT_OPTION
SERVER_PRINCIPAL	101	SERVER_PRINCIPAL	354	0	2	354	IM  	IMPERSONATE	W	GRANT_WITH_GRANT_OPTION
SERVER_PRINCIPAL	101	SERVER_PRINCIPAL	354	0	2	354	VW  	VIEW DEFINITION	W	GRANT_WITH_GRANT_OPTION
SERVER_PRINCIPAL	101	SERVER_PRINCIPAL	354	0	288	354	IM  	IMPERSONATE	G	GRANT

NAME	type_desc
public	SERVER_ROLE
EU\ntcanalytical.im	WINDOWS_LOGIN

What I have tried:

Do you know what should I do with these?