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?