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:-
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.
DROP LOGIN [Test2]
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
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
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')
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.
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.
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
Support Engineer, Microsoft SQL Server
Reviewed By, Mukesh Nanda, TL, Microsoft SQL Server
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..
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.