User name changed in active directory, SQL Server shows old user name - FIXED!
-
I have googled and it seems that this is a known issue - however I can find no guaranteed fix. A person needed their name changing so the admins changed their name in AD and all seemed to go fine. However I noticed that the SQL Server stored procedures using
system_useroruserare still returning the old name from AD. There are some posts out there on google acknowledging that the SID maintains the link between AD and SQL Server however the name in SQL Server is not updated. I am loath to bounce the server as it is used pretty much 24/7(and apparently it is not guaranteed to work). So I am wondering if anyone else has seen this and knows how to get the name in SQL Server to pick up the new AD name(the user logs on via a group policy rather than as themselves). [edit] See SUCCESS post below for information regarding fix.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I have googled and it seems that this is a known issue - however I can find no guaranteed fix. A person needed their name changing so the admins changed their name in AD and all seemed to go fine. However I noticed that the SQL Server stored procedures using
system_useroruserare still returning the old name from AD. There are some posts out there on google acknowledging that the SID maintains the link between AD and SQL Server however the name in SQL Server is not updated. I am loath to bounce the server as it is used pretty much 24/7(and apparently it is not guaranteed to work). So I am wondering if anyone else has seen this and knows how to get the name in SQL Server to pick up the new AD name(the user logs on via a group policy rather than as themselves). [edit] See SUCCESS post below for information regarding fix.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I suspect you'll need to rename the login within SQL. Try:
ALTER LOGIN [Domain\OldUsername] WITH Name = [Domain\NewUsername];
ALTER LOGIN (Transact-SQL)[^]:
If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I suspect you'll need to rename the login within SQL. Try:
ALTER LOGIN [Domain\OldUsername] WITH Name = [Domain\NewUsername];
ALTER LOGIN (Transact-SQL)[^]:
If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks, the thing is they log in under a group i.e.
sql_production_grouprather than their own login. So unfortunately there is no login associated with them outside the group level. We have some 100+ people associated with this group and to change the group login would be too much of a pain for the admins. [edit] Ooh, I just had a thought! If I create a new group and associate them with that, this may force SQL Server to refresh the name associated with the SID - I will try this tomorrow.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Thanks, the thing is they log in under a group i.e.
sql_production_grouprather than their own login. So unfortunately there is no login associated with them outside the group level. We have some 100+ people associated with this group and to change the group login would be too much of a pain for the admins. [edit] Ooh, I just had a thought! If I create a new group and associate them with that, this may force SQL Server to refresh the name associated with the SID - I will try this tomorrow.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I've seen a few people suggesting that resetting the token cache resolves the issue:
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
Another suggestion is to create and then drop a login for the old username[^]. There's also a suggestion to disable the server's local SID cache[^], but that needs a restart anyway, and could have a negative effect on performance.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I've seen a few people suggesting that resetting the token cache resolves the issue:
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
Another suggestion is to create and then drop a login for the old username[^]. There's also a suggestion to disable the server's local SID cache[^], but that needs a restart anyway, and could have a negative effect on performance.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I tried the create and drop user suggestion, however I did not run a query under the user. It may be that a query needs running under the user for the SID to be refreshed. I will try the cache refresh - I didn't try that as it will apparently slow things down for a bit(I should write the technical manuals with descriptions like that). I will try these two things tomorrow and report back on the results(can't be fagged to log in from home tonight). Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I've seen a few people suggesting that resetting the token cache resolves the issue:
DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
Another suggestion is to create and then drop a login for the old username[^]. There's also a suggestion to disable the server's local SID cache[^], but that needs a restart anyway, and could have a negative effect on performance.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Success! I tried the freecache and created a user under the old name and ran a query under that old name. I then deleted the user and all is fine now. The
system_useris picking up the new user name. The one thing I did not do is check with the user if the SID had been refreshed overnight, before I made these changes, and whether everything was fixed before I made these changes. Anyway thanks for you help and for replying to my call for help so quickly.:thumbsup: [edit] subject amended“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
