Error 15173 while we tried to drop one of our Windows Login from SQL Server, part of our house-keeping task, and SQL Server prevent us from dropping the Windows Login even though we have unmapped all the database mapped and owned by the login. It works fine for us before this.
Msg 15173, Level 16, State 1, Line 2
Login 'DOMAIN\User_Login' has granted one or more permission(s). Revoke the permission(s) before dropping the log
After some research, we found a solution for our issue from the SQLServerCentral’s forum with regards to drop the Windows Login from SQL Server due to ENDPOINT permission. But our SQL Server was not configured with mirroring.
We try to execute the following queries and we realized that we obtain the same output as the one shown in the threat.
SELECT * from sys.server_permissions WHEN grantor_principal_id = (Select principal_id FROM sys.server_principals where name = N'DOMAIN\User_Login');
To obtain the mirroring endpoint name.
SELECT * FROM sys.[database_mirroring_endpoints];
Drop the mirroring ENDPOINT. The ENDPOINT name retrieved from earlier query.
DROP ENDPOINT <ENDPOINT_NAME>;
Next, drop the user again, and it succeeded.
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DOMAIN\User_Login') DROP LOGIN [DOMAIN\User_Login];