Are you experiencing "Microsoft SQL Server Error 15173"? This article will help you fix this error.

Permission issues with a login of an SQL Server can trigger SQL error 15173. This error is usually experienced when a login with server level access give permission to any server principal.

As part of our Server Support Services here at Ibmi Media, we regularly help customers fix SQL related issues.

In this context, we shall look into the causes of this error and the possible solution.

More about Microsoft SQL Server Error 15173?

When this error occurs, you will see an error message such as;

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)

Apart from permission issues, most users also start to see this error when they configure AlwaysOn availability group or database mirroring.

How to fix Microsoft SQL Server Error 15173

Fixing this issue can be approached in different ways as you will see below.

1. Using SQL Server Management Studio (SSMS) to revoke Login access.

Follow the following steps;

i. To begin, make a connection to the SQL Server Instance with the SQL Server Management Studio.
ii. Next, Click on the security folder followed by the logins folder.
iii. Here, do a double click on the login you want to revoke and navigate to securable which is at the left side pane of the window.
iv. Now, uncheck the login permissions and click on OK to effect changes.

2. Using T-SQL

To use T-SQL to revoke a login's access, let's say we are trying to drop a login (ijk), use the command below;

USE master

REVOKE VIEW DEFINITION ON LOGIN:: ijk FROM [abc]

After this stage, drop the login with the command below;

DROP Login ‘LOGIN_NAME’

3. Revoking Login access for the endpoints

Run the following command;

USE Master

Go

REVOKE CONNECT ON ENDPOINT:: [hadr_endpoint] TO [abc]

Where abc here represents the grantor.

Need support in fixing SQL errors? We can help you.