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

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

  • Comments 2

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
 

 

 

 

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Is there a T-SQL command to change the grantor. If that is available that should be a easy way to drop the login

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

Page 1 of 1 (2 items)