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.



clip_image001



T-SQL statement


USE MASTER


DROP LOGIN [Test2]


Message text


Drop failed for Login ‘Test2’.  (Microsoft.SqlServer.Smo)


For help, click: http://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: http://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




 clip_image002

Select All Objects of the types – Logins



clip_image002



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



clip_image002[7]



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


clip_image001[6]



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

  1. djay says:

    Is there a T-SQL command to change the grantor. If that is available that should be a easy way to drop the login

  2. vishal rajput - IBM says:

    Excellent .. this really helped in solving problem!!!!! Thank you..

  3. Jim says:

    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.