Click here to Skip to main content
15,039,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
SQL
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?
Posted
Updated 31-Jan-20 1:57am

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900