Drop failed for Login since it has granted one or more permission(s)

Suppose you encounter the following scenario:-

SQL Server 2005 login ‘Test2’ is a member of only Public server role and is not mapped as a User in any Database, nor does it own any Schema.

We can verify that the login 'Test2' is not mapped as a user in any Database:-

USE MASTER

sp_helplogins 'test2' LoginName DBName UserName UserOrAlias

-> The output is blank.

When we try to drop this login (DROP LOGIN test2), it fails:-

Drop failed for Login ‘Test2’.

Login ‘Test2’ has granted one or more permission(s). Revoke the permission(s) before dropping the login.

T-SQL statement

USE MASTER

DROP LOGIN [Test2]

Message text

Drop failed for Login 'Test2'.   (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Login 'Test2' has granted one or more permission(s). Revoke the permission(s) before dropping the login. (Microsoft SQL Server, Error: 15173)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=15173&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Reason

Since Test2 exists only as a login at Server level, we query if Test2 has granted permissions to any other Server Principal using the following script :-

Select * from sys.server_permissions

where grantor_principal_id =

(Select principal_id from sys.server_principals where name = N'Test2')

Output

class class_desc major_id grantee_id grantor_id Type Permission_name state state_desc

101 SERVER_PRINCIPAL 277 276 277 VW VIEW DEFINITION G GRANT

276 was the principal_id for ‘Test1’

277 was the principal_id for ‘Test2’

From books online :- View Definition enables the grantee to access metadata of Procedures, Service Broker queues, Scalar and Aggregate functions, Synonyms, Tables, Table-valued functions, Views.

Resolution

We should verify if login Test1 has been granted any permission by login Test2 using Management Studio :-

Security – Logins - right click on login Test1 - click on Properties - Login properties–Test1 – Securables

Click on the Add button - you will prompted with the following dialog box

Select All Objects of the types – Logins

Select the login Test2 – click on the Effective Permissions… button.

The next screen will verify the permission “VIEW DEFINITION” for Test1:-

We should revoke the View Definition permission from the login Test1 granted by login Test2 on Test2 by deselecting the GRANT checkbox. 

T-SQL statement

USE MASTER

REVOKE VIEW DEFINITION ON LOGIN:: Test2 FROM [Test1]

Now we should be able to drop the login Test2.

 Regards,Rahul Digwasiya  Support Engineer, Microsoft SQL Server Reviewed By, 
Mukesh Nanda, TL, Microsoft SQL Server