Fix SQL Server Error 15173: Revoke the permission(s) before dropping the login.
A login was failed to drop from a SQL Server instance with an error code 15173. As Microsoft SQL Server error 15173 suggests that this login has granted some permissions to other logins. Let us check the details and then fix this issue so that you can drop such logins without any issue.
Microsoft SQL Server Error 15173
Login was failed to drop because of SQL Server error 15173. This error comes when a login having server level rights has granted permission to any server principal. The details of Microsoft SQL Server error 15173 is given below.
Login ‘xyz’ has granted one or more permission(s). Revoke the permission(s) before dropping the login. (Microsoft SQL Server, Error:15173)
You can check the details about the grantee by running below command. You will see the grantee_principal_id and grantor_principal_id column along with other details. Grantor_principal_id will be the id of that login which you want to delete. You can see class_desc and permission_name column to see the permission type and permission category.
SELECT * FROM sys.server_permissions
Where grantor_principal_id= (Select principal_id from sys.server_principals where name = 'xyz')
You might get such issues if you have configured AlwaysOn availability group or database mirroring because there might be possibility that your login has granted some permission to endpoint so you need to revoke that permission to fix this issue.
Solution
The solution to fix this issue is to revoke the access which you have granted. Run below command to revoke the access for endpoints. This solution is valid only in case if you have granted access on endpoint. If you have granted access on login then you should keep reading this article.
--Here xyz was grantor.
--Make sure to revoke the access level which you get in the output of above script.
--You can get this by focusing on permission_name and state_desc column. In my case, it was view definition and state_desc was GRANT.
USE Master
Go
REVOKE CONNECT ON ENDPOINT:: [hadr_endpoint] TO [xyz]
Once command will be executed successfully, you are able to drop that login. Do let me know in comment section if you are still facing the issue. You can try below T-SQL to revoke the access from login if you get an output for logins not endpoint.
If this access is assigned to login and not endpoint then you should run below command to revoke the access. Here we are trying to drop login xyz but it was failed because xyz login has granted access to login abc. So, we will revoke the access to drop the user.
USE master REVOKE VIEW DEFINITION ON LOGIN:: xyz FROM [abc]
Go ahead and drop the login which you wanted to drop.
--change the name of Login_name with your login name.
DROP Login 'LOGIN_NAME'
You can also revoke this access level using SSMS/GUI as well. Connect to SQL Server Instance in SSMS, Expand security folder followed by logins folder. Double click on the login and go to securables from left side pane. You can uncheck the permissions allocated to this login and click on ok to apply the changes.
You can click on attached link if you want to learn about another Error 15174 which comes during drop Login statement.
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
