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)
------------------------------
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