SQL SERVER – FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

There are parts of SQL Server where I always like to take expert advice. Sometimes a quick call to a friend can save you tons of time. When I see topics around AlwaysOn, though I know what the basic steps are to building and working with AlwaysOn, the hardcore troubleshooting is not the forte perse. But when I encounter questions around it, I put my learning hat to explore the possible reasons. There was a mail from one of the readers which has been on my Inbox for couple of weeks and I wanted to find the answer. His question goes like this:

While working with AlwaysOn availability group and cleaning it up, I am encountering below error while dropping a login.

DROP LOGIN Pinal
GO

Msg 15173, Level 16, State 1, Line 1
Server principal ‘Pinal’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

SQL SERVER - FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal drop-error-02

Solarwinds

Even trying from UI also gives same error

SQL SERVER - FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal drop-error-01

Next task was to find what is causing the error, so I made below query which can tell the exact objects that are dependent and making our DROP command to fail.

SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal')

SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal'))

SQL SERVER - FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal drop-error-03

Based on output, it means there is an endpoint on which Pinal has been granted permission. Second result shows that I have used my account i.e. Pinal to create endpoint and granted permission to AppUser account using below command:

CREATE ENDPOINT [hadr_endpoint] STATE=STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

As the error message suggested, I revoked permission using the below command:

REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

After the above step, I was able to delete login. Yes, this was a great learning for me today. Do let me know if you ever encountered similar errors in your environments?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Fix – Error: Msg 468, Level 16, State 9, Line 1
Next Post
SQL SERVER – Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Related Posts

11 Comments. Leave new

  • I ran into the same problem. In my case it maked more sense to just change the owner of the endpoint to sa, for example
    alter authorization on endpoint::Hadr_endpoint to sa

    After this I was able to drop the login.

    Reply
  • I ran into the same problem. In my case it maked more sense to just change the owner of the endpoint to sa, for example
    alter authorization on endpoint::Hadr_endpoint to sa

    After this I was able to drop the login.

    Reply
  • David N Nguyen
    January 7, 2016 5:47 am

    I’m having same problem and I have tried your script to detect the problem and opted to alter authorization to sa instead of revoking because I’m concerned that login will not be able to function as before. And I was able to drop that login. Thanks.

    Reply
  • Pinal, great post. Also, good job by ijeb for the ALTER AUTHORIZATION idea. That was all I needed, in my case. I’m thinking this is something to check anytime a new AG is created. I’ve added it to my checklist for Availability Group installation.

    Reply
  • This was the exact situation for,me this one helped to resolve ,Thanks

    Reply
  • Hi Pinal – Could you help understand what permissions I need to revoke for this login in order to drop.

    class_desc class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
    SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 AL ALTER G GRANT
    SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 CL CONTROL G GRANT
    SERVER_PRINCIPAL 101 SERVER_PRINCIPAL 301 0 2 301 IM IMPERSONATE G GRANT

    public SERVER_ROLE

    Reply
  • Hi Pinal ,

    I’m getting below error while dropping the Logins
    The server principal owns one or more availability group(s) and cannot be dropped. (Microsoft SQL Server, Error: 15141)
    Need help

    Reply
  • Dropping AppUser account should create more problem. Changing ownership of endpoint is real fix.

    ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;

    Reply

Leave a Reply

Menu