Server principal ‘LoginName’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

A good DBA likes to keep his/her SQL Server environment clean and neat, that’s why dropping old, unused or unnecessary logins and users is always fun. However, dropping a user or login can cause many unexpected troubles if you don’t carefully observe the permissions and roles and objects that could be related to that account. Unfortunately, there is no easy way to track down or analyze all server or database level permissions, users and roles related to one specific login.

I have scripts which runs every hour and checks and stores all roles and permissions to inventory tables. Before I clean up my logins, i always have a look at these tables to prevent any possible problem.

Today I was doing my routine task but i couldn’t drop one domain account and SQL Server returned this generic error :

loginfail

I am surprised because I double check my tables, there shouldn’t be a object or role or permissions bound to this account. The problem was this account was the grantor of a permission, not the grantee. I looked at my tables and I found that this account granted “CONNECT” permission on Always On Endpoint to another account.

This can also be checked with the below query:

SELECT * FROM sys.server_permissions
WHERE grantor_principal_id = ( SELECT principal_id FROM sys.server_principals WHERE NAME = N’LoginName’)

Error: 18456, Severity: 14, State: 5 Login failed for a member of AD group

This is officially my first blog post. I was planning to write a blog for the last 2 years but couldn’t start until now, so here we go.

Last Friday, we ran into a weird login failed error message for a login who is a member of an AD group with sysadmin rights on a new installed SQL Server. I tried conventional methods to find root cause and fix it but I have never experienced this case before. So, I decided to write a blog post about it as my first post!

One of my colleague reported that he can not login into a newly installed Always On Secondary SQL server(let’s call it SQL2) from another database host(let’s call it Host1). I checked error log immediately and saw this errors:

blogLoginFailsAD

This is a typical login failure which a dba sees a lot, however the problem is he is a member of an AD group(let’s call it [AD\Group1]) and [AD\Group1] exists on SQL2 with sysadmin privileges! Furthermore, I am also a member of [AD\Group1] and I can connect to SQL2 as other members. Also, [AD\Group1] is exists on SQL1, primary replica of this Availability Group, and he connected that server without any problem. Things were getting interesting.

First, I created his domain account directly, he was able to connect both SQL1 and SQL2. This way, we eliminated the possibility that his account is not able to login. But why he couldn’t connect through an privileged AD group? There was something going on with AD group and his membership. I used xp_logininfo procedure to retrieve information about [AD\Group1] members and I saw him as a member, so why the hell SQL does not allow him to login and says there is no such login?

I patched both servers with latest SP and CU, didn’t help.

We removed his account from AD group and added him again, didn’t help.

We removed [AD\Group1] from SQL2 and created again with sysadmin role, didn’t help.

Even though I am embarrassed to tell this, I restarted SQL service, of course didn’t help.

I further searched this issue on internet, also didn’t help.

Then, I installed SSMS on SQL2 host and asked him to try to connect directly from database host, this time it worked. Now, I am even more confused.

So, We have an AD group with sysadmin role which contains 2 members in it. One of them can connect to both SQL1 and SQL2 from anywhere(Host1, local workstation, SQL2 host and so on) without problem, other member can connect to SQL1 without a problem. He can also connect to SQL2 as a member of [AD\Group1] from his workstation and SQL2 host. However, he can’t connect to SQL2 from Host1.

I realized that this needs more careful and detailed examination. I went back to basics and read about AD groups, kerberos authentication and SQL Server authentication-authorization. Then, my colleague told me that he removed himself from [AD\Group1] one week ago and re-added 1 day before this incident occurred. That moment I realized that his kerberos ticket was different on AD than the one cached in his user profile on Host1 server. I asked him to log off from Host1 and log on again, only then he could connect to SQL2 from Host1.

To sum up, If you encounter a similar login failure make sure that domain user is logged off and logged in again!