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
Comments
Anonymous
September 06, 2011
Is there a T-SQL command to change the grantor. If that is available that should be a easy way to drop the loginAnonymous
August 18, 2013
Excellent .. this really helped in solving problem!!!!! Thank you..Anonymous
June 23, 2015
The above description and steps are flawless!! Great job this worked like a champ. My situation was tied to end points and not logins. This worked perfectly.