3

We changed the username of one of the ActiveDirectory users, but now, days later the old username is still showing in SQL Profiler when they login. Are these SIDs cached locality with a copy of the username? Can I clear this cache?

The user does not have "direct" access to SQL server, but is in a local security group which has access to the server.

2

There is caching taking place in SQL. Here is how to see it in SQL Server 2008 R2 and how to reset it. This is for a Windows user that is in an AD group and where the group has been added as a login in SQL. The user has not been added directly as a SQL login.

connect with original username

Connect to SQL as a user. Let's say the user is DOMAIN\test connecting with SQL Server Management Studio. Check logged in user info.

select SYSTEM_USER

Output: DOMAIN\test

sp_who2

Output includes column the Login which has rows for: DOMAIN\test

Object Explorer > right-click server at the top of the tree > Properties Connection shows: DOMAIN\test

You can additionally see the username referenced here: Object Explorer > right-click server at the top of the tree > Activity Monitor > Processes

and here: Object Explorer > SQL Server Agent > Job Activity Monitor

connect with new username

Now change the AD username to test2. Reconnect in SQL Server Management Studio. Only the connection properties box shows the new DOMAIN\test2 username. If the server/service has not been restarted or a timeout/reset didn't otherwise take place, the old username will still show for the other outputs.

reset the connection

Even if you log out and let all connections end or if you kill them, when you log back in as test2, it will continue to be referenced as the original username. Here is one way to force a reset without restarting the server or SQL service. Create a new SQL login directly for the DOMAIN\test2 account. Immediately delete it. There is a check that takes place and flushes the cached entry. Now when you connect as test2, all the checks above reference the new username.

| improve this answer | |
1

It's not cached on the SQL Server - you can see it in the system view:

SELECT name, sid FROM sys.server_principals;

You can update the name for the LOGIN by running the ALTER LOGIN statement, similar to:

ALTER LOGIN OldName WITH NAME = NewName; 

Run the SELECT statement above to see the desired change.

| improve this answer | |
  • I forgot to mention: The user has not been added directly to SQL server, but is in a local security group. – NTDLS Feb 25 '10 at 17:08
  • 1
    Not 100% sure then. I'd try running xp_logininfo 'DOMAIN\GROUP', 'members' and see if there is a disparity between that user's name and his mapped login name. The mapped login name is what SQL Server is using. – Brian Knight Feb 25 '10 at 18:51

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.