AD account change not effective on sql server

Question

When we have to change a user's last name lets say from jsmith to jjones, in our Win2000 active directory, we experience a bit of strange behavior with Sql Server 2008.

When jjones hits the server via this application, Sql Server still thinks their user name is jsmith, whereas everything else they have logged into post-name-change (i.e. their workstation, the application in question), accepts jjones. It is almost as if Sql Server caches the credentials.

To be clear, we are changing the last name only, and not recreating the account, so the SID should be the same (my assumption). They are also not logging into the server directly.

My 2 questions are: If the credentials are cached, where is it? And second, how do we clear said cache?

If my understanding is completely skewed, what am I missing?

TIA

Jim Little

Answer 1

SQL Server does cache the user tokens indeed. You can force them to clean up with

DBCC FREESYSTEMCACHE('USERSTORE_TOKENPERM');

Answer 2

That is correct. When you setup a login in SQL Server you give it the username and domain of the account. It then goes to Windows and gets the SID of the account and stores both in the SQL Server. When you change the username in Windows, the SID stays the same. When the user logs into the SQL Server the SID is passed from the client machine to the server, and verified against the database (along with some authentication tokens).

The SQL Server uses the name that it has stored in the master database as the display name.

To change this you'll need to remove the login from the SQL Server and recreate it using the users new name.