One of our users' names was legally changed, so we changed their Active Directory username to match – from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a stored procedure, it returns the old name, not the new one.
Googling led me to KB 946358 which suggests that their name is being cached on the server and not updated, presumably because suser_name() is calling LsaLookupSids. However, the workaround in that article involves restarting the server, and even if it was I'd still like to understand the problem.
If I change my context to theirs, the correct name comes back:
EXECUTE AS LOGIN = 'domain\newname'
GO
SELECT suser_name() --returns 'domain\newname'
…I would have assumed that this would also call LsaLookupSids, and so would return the incorrect name. It seems likely that I don't really understand the mechanisms at work here.
Some observations that may matter:
-
This user doesn't have an explicit login on the server. But they are a member of an AD group that does. The changed name (domain\newname) appears in the result set for
exec xp_logininfo 'domain\ADGroupName', 'members'; domain\oldname does not. -
The user is calling suser_name() from within a stored procedure, called from a passthrough query in an Access 2003 MDB.
-
We've changed plenty of users' account names in the past, but have only observed this issue in the last week (two changes were made in last week, both seem to exhibit the issue).
-
The server is running Sql Server 2008 SP3 x64 on Windows 2008 R2 Datacenter edition.
What's going on? As the DBA, what might I do or where might I look to solve this?
Best Answer
Could this be related to caching with Kerberos? (just a guess though might be unrelated) http://blogs.technet.com/b/tspring/archive/2014/06/23/viewing-and-purging-cached-kerberos-tickets.aspx