SUSER_SNAME() Returning the Wrong Login

If users are logging into your SQL Server with their Active Directory accounts, you may run into an issue if their login changes. This issue is usually associated with someone changing their account name to reflect a change in their legal name. If the user executes the function SUSER_SNAME(), it may return their old login.

There is some caching that takes place on the server which is holding onto the “key” associated with that account. When a user has their account name changed, Active Directory will not update this “key”, only the “name”. Any server that has cached the account may retain the old account name. When SQL Server processes the SUSER_SNAME() call, the old account name is returned.

Rebooting the server has been shown to resolve this issue but there may be a better way. If your users are obtaining access through an Active Directory group (which they should be), simply creating a login for that specific user and then dropping it will resolve the issue.

Creating the login will require the server to validate the account “name” against Active Directory, during which the cached record will be over-written. Dropping the login will return the instance configuration to it’s original state, where the user obtains access via their group.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s