10

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?

  • Is the MSSQLSERVER (or whatever the instance name is) service logging on as Local System or a real Login? The value might be cached in the registry of the account running the lookup. In your case, you were logged in and making the request. I am thinking that maybe if you are using a regular account to run SQL Server (as one should) then maybe log onto SQL Server as the "SQL Server" login, then run your EXECUTE AS and SELECT SUSER_NAME() test. Also, have you tried SUSER_SNAME() and any of the other 100 variations? – Solomon Rutzky Jan 15 '16 at 20:14
  • Try creating a login on the instance using the new name. This won't have any affect on their permissions. Run SUSER_SNAME(), it should be fixed at that point. You can then try dropping the login and see if it keeps the new name. – Kenneth Fisher Jan 15 '16 at 20:38
  • @srutzky It is a default MSSQLSERVER instance running under a domain account. Unfortunately I don't have the password to log in with it. I have not tried suser_sname() as the user yet, I believe that it is the same as suser_name() if given no arguments. It's worth a try though - thanks! – Warrior Bob Jan 15 '16 at 22:36
  • 1
    SQL Server matches all accounts by the SID - whether SQL or domain. Since domain SIDs come from active directory, changing the name doesn't change the SID. If it was cached, the old name will be returned. If a login already exists, the name of the login will be returned whether it's still the same name or not, as long as the SIDs match. This is the same for databases users. – Sean Gallardy Jan 18 '16 at 4:47
  • 1
    You might try running ipconfig /flushdns and ipconfig /registerdns from a command line to see if that clears up the problem. – RLF Jan 22 '16 at 15:29
2

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

  • 1
    I can't say for sure this is the issue, but I believe this or something like it is. Rebooting the server (which happened for separate reasons) seems to have cleared it up, at least for this case. It's not clear if it will come up again. – Warrior Bob Jan 21 '16 at 22:50
  • That's a good suggestion, I should have thought of that! :-) That was what we did before actually to clear that up cached kerberos issues. Glad you were successful! – Normoe Jan 22 '16 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.