Troubleshooting specific Login Failed error messages

We have faced these issues several times with many reasons like Cluster issues, network problems, Domain issues, Login Transfer Issues.

But apart from all these issues there are some uncommon issues as well, one of which i am explain below.

I came across one this issue recently

Issue:
User X is not able to login to SQL Server (application or SSMS).The user was able to login to SQL Server before the issue started occurring from … Date.

Logon Error: 18456,Severity: 14, State: 11.

Logon Login failed for user
<Server name>\Name’. Reason: Token-based server access validation failed
with an infrastructure error. Check for previous errors. [CLIENT:]

Login Failed for User <ServerName>\Username’?

Reason:

We have added a User from AD (Windows Integrated) to SQL Server as Login, Later due to some reason we have to drop that user from the AD and created it again. But we did not drop the user from SQL Server.

The SID of the user has changed on the AD whereas SQL Server still has the old SID which invoked the error.

Login Error State 11 means “Valid login but server access failure”, which further points that SQL Server Login is valid but missing certain security privileges which would grant access to the instance.

Resolution

1. Look into the SQL Error log and verify that that the login failed message for the user has a State 11. You can alternatively verify the state number from the SQL Server default traces as well (available from SQL Server 2005 and above).

2. Next look into the Ring Buffers output and find out what was the API that failed.

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, dateadd (ms, (a.[Record Time] – sys.ms_ticks), GETDATE()) as [Notification_Time], a.* , sys.ms_ticks AS [Current Time] FROM (SELECT x.value(‘(//Record/Error/ErrorCode)[1]‘, ‘varchar(30)’) AS [ErrorCode], x.value(‘(//Record/Error/CallingAPIName)[1]‘, ‘varchar(255)’) AS [CallingAPIName], x.value(‘(//Record/Error/APIName)[1]‘, ‘varchar(255)’) AS [APIName], x.value(‘(//Record/Error/SPID)[1]‘, ‘int’) AS [SPID], x.value(‘(//Record/@id)[1]‘, ‘bigint’) AS [Record Id], x.value(‘(//Record/@type)[1]‘, ‘varchar(30)’) AS [Type], x.value(‘(//Record/@time)[1]‘, ‘bigint’) AS [Record Time] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’ ) AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys ORDER BY a.[Record Time] ASC Output of the query  in Case of Error will be as below Calling API Name: NLShimImpersonate API Name: ImpersonateSecurityContext Error Code: 0x139F (The group or resource is not in the correct state to perform the requested operation)

3. Check if that login is directly mapped to one of the SQL Server logins by looking into the output of sys.server_principals.

4. If the login is directly mapped to the list of available logins in the SQL instance, then check if the SID of the login matches the SID of the
Windows Login.

If the result in setup 2 is as mentioned drop the SQL Server login and create it again.

Try to fix the Orphaned User. Search in BOL Orphaned User

Drop Failed for User

It’s a very common issue which every DBA faces in there daily administrative activities. When we restore a database along with it few users comes along, which own’s permission on database objects.  These user’s are orphaned which need to be deleted.

While deleting these user we faces a very common error

“DROP LOGIN FAILED FOR USER ‘XYZ’”.

The database principal has granted or denied permissions to objects in the database and cannot be dropped.

To resolve these kind of  issue we struggles a lot and spend our precious time in these type of activities.

Here we will see how to over  come these issues without spending much time.

Once the database is restored check for Orphaned Users

USE <Databasename>;
GO
sp_change_users_login @Action=’Report’;
GO

Once Users are identified we will figure out what permission they hold in the database

1).select permission_name,state_desc,object_name(major_id) as securable,

user_name(grantor_principal_id) as grantor

from sys.database_permissions

where grantee_principal_id = user_id(‘XYZ’)

2.)select * from sys.database_permissions

where grantor_principal_id = user_id (‘XYZ’);

 

Once user permission are identified revoke the permission.

REVOKE the defined permission from step1.

REVOKE the impersonate permission from user ‘XYZ’ .

What is Impersonation?

SQL Server supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement, or implicitly by using the EXECUTE AS clause on modules. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

Implicit impersonations that are performed through the EXECUTE AS clause on modules impersonate the specified user or login at the database or server level. This impersonation depends on whether the module is a database-level module, such as a stored procedure or function, or a server-level module, such as a server-level trigger.

REVOKE IMPERSONATE ON USER::labelsecurity TO public

The above statement will execute successfully, but still not able to drop the user same error as mentioned above.

Execute the Query in step 2 , get the granter_principal_id then pass the same in below query to get the user name who impersonated the user.

SELECT USER_NAME(granter_principal_id)

use [Database_Name]
GO
REVOKE IMPERSONATE ON USER::[XYZ] TO [RU] AS [XYZ]
GO

Now try dropping user again.