Why suser_name() might not reflect the correct AD name?

teamwork

You have the ability to query the current user name in SQL Server using a basic Transact-SQL statement:

SELECT suser_name()

The problem you might eventually see is the incorrect name returned, if that users name was recently changed. Active Directory is a great product, but you might not understand why changing that persons name in AD (because of divorce, marriage, etc.) might not result in you seeing that new name in your SQL Server queries.

The explanation is simple, but it can be difficult to address.

The local security authority (LSA) caches the mapping between the SID and the user name in a local cache on the you SQL Server computer. The cached user name is not immediately synchronized with your domain controllers. The LSA on the SQL Server computer first queries the local SID cache. If an existing mapping is already in the local SID cache, the LSA returns the cached user name information instead of querying your domain controllers. This behavior is intended to improve performance.

The cache entries do time out, however chances are that recurring queries by applications keep the existing cache entry alive for the maximum lifetime of the cache entry.

If this turns out to be something that causing you issues in your environment, you can disable caching on your SQL Server instance, but you must also understand that will cause increased domain controller workload and network traffic.

To work around this issue, disable the local SID cache on the domain member computer. To do this, follow these steps:

  1. Open Registry Editor by typing regedit in the Start Search box, and then press ENTER.
  2. Locate and then right-click the following registry subkey:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
  3. Point to New, and then click DWORD Value.
  4. Type LsaLookupCacheMaxSize, and then press ENTER.
  5. Right-click LsaLookupCacheMaxSize, and then click Modify.
  6. In the Value data box, type 0, and then click OK.
  7. Exit Registry Editor.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s