Home Forums SQL Server 7,2000 Security Login failed for user with token-based server access validation error RE: Login failed for user with token-based server access validation error

  • Just solved an issue like this here.

    Our client had a group in AD mapped to a SQL Server login, mapped to a database user. For various reasons, they decided that the group needed to be changed from a Global Security group to a Domain Local Security Group. They achieved this by renaming the group and creating a new one with the original name.

    SQL Server seemed to partially cope with this. Users in the group could log in, but would intermittently get a token-based server login error. So, it appears the database user was partially orphaned. SQL Server showed the correct Login name mapped to the user. I didn't want to recreate the database user, because it had custom, database-level permissions.

    The solution:

  • Drop the login. At this point SQL Server showed that the user was mapped to the renamed AD group
  • Recreate the login
  • Apply any relevant server-wide permissions and config to the recreated login
  • Map the db user to the recreated login
  • Correct database username if required (for some reason SQL Server prefixed my user with the domain name, which I didn't want)
  • Before executing this script:

  • Ensure you have made the appropriate backups prior to execution
  • Ensure any server specific login config/permissions is recorded prior to execution
  • Replace EXAMPLE_DOMAIN, ExampleGroup and ExampleDb as applicable
  • Script:

    USE [master]

    DROP LOGIN [EXAMPLE_DOMAIN\ExampleGroup]

    GO

    -- At this point SQL Server showed that the user was mapped to the renamed AD group

    CREATE LOGIN [EXAMPLE_DOMAIN\ExampleGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[ExampleDb]

    GO

    USE [ExampleDb]

    ALTER USER [ExampleGroup] WITH LOGIN = [EXAMPLE_DOMAIN\ExampleGroup]

    GO

    -- Correct database username if required