Hi,
I have a situation where a user has had their windows login name renamed in AD. I believe their SID stayed the same just the name changed. This causes some problem in SQL. When I try looking for the new user in SSMS (Domain\New_user) its not there.
If I query sys.database_principals for the new user its not there.
If I try and create the new user it fails with user already exists
If I try and Drop the new user it fails with No user with that name
If I query for the SUSER_SID for the new user I get one! But then when I query sys.database_principals with that SID it comes back blank.
I finally found if I used this query I got the old user name that is in SQL
select * from sys.server_principals
where sid = (SELECT SUSER_SID('Domain\New_User'))
So my question is does anyone know how I can update SQL with the new_User(this is a windows user not SQL user)? Or do I have to script out all the security for the old user name drop the login and then readd it with the new user name and apply all the security?
I tried EXEC sp_change_users_login 'Auto_Fix', 'Domain\New_User' and I tried with the old user name but it didn't help.
Anyone got any other ideas?
Thanks!
You should just be able to simply change the name using an ALTER LOGIN command.
The original "old" name should still be present in the views as your getting duplicate errors, so it would just be a case of renaming this in the GUI, or by doing
ALTER LOGIN [Domain\OldName] WITH NAME [Domain\NewName]
But really should be advocating for security groups over individual logins, makes life so much easier when managing permissions and the chance of an AD group changing its name is minimal over end users.
December 22, 2022 at 5:31 pm
Thank you Ant-Green! I got totally hung up on the Windows login vs SQL Login and didn't think I could change it with that alter command. That worked. For anyone else reading this I'll just up date the syntax with an = ALTER LOGIN [Domain\OldName] WITH NAME = [Domain\NewName].
Merry Christmas!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply