Authenticate Issue in SQL Server due to SID

Found this to be useful so sharing the same.

User is unable to access the database even though his account is existing in the Group and having relevant access.

Cause:

Before Moving to one of the cause let me take you through how authenticate process is done for authenticating to SQL Server using a Domain Account

Process of SQL Authentication:

Whenever a Domain User is logged on to a Machine his SID is being cached in the Machine. When this User Authenticates with SQL Server the below mentioned process is carried out for Authentication.

1. Domain User ID and Password along with SID for Domain Account are passed in.
   
2. The User ID and Passwords along with SID received are Authenticated with the User ID and Password along with SID which is existing on SQL Server.
   
3. When all there are verified successfully the respective existing access is granted to that particular user.
   

Why the problem is occurred:

Basically the groups in Windows can be classified as Domain Global and Domain Local.

The existing Domain Groups may on Domain either of the Hierarchy i.e. Domain Global and Domain Local.  These Domain Group if dropped and Same Domain groups were created at Domain Local / Global Hierarchy then u will come across the issue for sure if this activity is done only at windows level but not at SQL Server.

Now here is where the tricky Part Lies.

Whenever a Domain Account or Group is dropped and Re-Created a New SID is assigned to that Login.

So when this New Login with same name is trying to authenticate with SQL Server the Authentication fails because only the User ID and Password match with that of existing in SQL Server but not the SID because At windows level it is having the new SID and at SQL Server the Old SID which one is Prior to the New Windows login is created.

To resolve the Issue the existing Group/Logins on SQL Server Needs to be dropped and Re-Created so that the New SID gets Synced and Updated and the Authentication can be done successfully.

You can get the Invalid Logins using Stored Procedure sp_validatelogins. But again this is not trust worthy because if an it checks only for SID Matching. Suppose if a Login is dropped and created again this will not show correct results as it compares only SID.