‘Microsoft SQL server error 15173’ error occurs when a login having server-level access has granted permission to any server principal.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

What causes ‘Microsoft SQL server error 15173’ error to occur

When a login fails to drop from a SQL Server instance then it throws an error 15173. This happens when the login having server-level rights has provided some server principal.

Sometimes, you may come across this error if you have configured AlwaysOn availability group or database mirroring. Because there might be some chances that your login has granted some permission to an endpoint which leads to this error.

For example, here is the error message that appears.

Drop failed for login ‘xyz’.
Login ‘xyz’ has granted one or more permission(s). Revoke the permission(s) before dropping the login. (Microsoft SQL Server, Error:15173)

How we fix the error ‘Microsoft SQL server error 15173’

Now let’s see the solution our Support Engineers provide to our customers to resolve this error.

The easiest way to resolve this error is to revoke the access that is been provided.

1. We run the below command to revoke the access for the endpoints.

USE Master
Go
REVOKE CONNECT ON ENDPOINT:: [hadr_endpoint] TO [xyz]

Note: In the above command, xyz is the grantor.

As a result of the above command, we will receive a script in which you can revoke the access. We do this by focusing on the permission_name and state_desc column.

2. We try the below T-SQL to revoke the access from the login if we get output from logins and not from endpoints. Here we are trying to drop the login xyz.

USE master
REVOKE VIEW DEFINITION ON LOGIN:: xyz FROM [abc]

Finally, we go ahead and drop the login by running the below command.

DROP Login ‘LOGIN_NAME’

3. We can revoke the access using SQL Server Management System as well. Here are the steps that we follow to revoke it.

• First, connect to SQL Server Instance in SQL Server Management Studio.
• Then expand the security folder and then the logins folder.
• Now, double click on the login and go to securable from the left side pane.
• Finally, we uncheck the permissions that are provided to the login. Then we click on OK to apply the changes.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, this error occurs when a login fails to drop from a SQL server instance. Today, we saw how our Support Engineers fix this SQL error.