It’s been some time since my last blog post! Changing a SQL Server Login name uising T-SQL.
Everyone who has tried at first, knows that it has its caveats. I couldn’t find a straight answer to the errors I was getting. You would think changing the sql login name should be simple. But, I was using the wrong approach.
First, we are working with “WindowsUser” login type. In SQLServer, I’m trying to fix a Windows User (local account)name from “dev01” to “devUser01” using the following T-SQL statement:
ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]
but, in doing so I got the following error message:
“Windows NT user or group ‘MXTLPT01\DevUser01’ not found. Check the name again.”
Of course, “MXTLPT01\DevUser01” Windows user account doesn’t exist on my system.
So, I created the user I try to run the T_SQL statement:
ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\Devtest01]
Nope, it didn’t not work ending with the following message:
“Msg 15098, Level 16, State 1, Line 31
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.”
Basically, It translate to your Windows Account SID Object in SQL Server doesn’t match the object you’re trying to change.
In other word, you need to work with the Windows User originally created in WINDOWS (Locally or Domain), has changed first before executing the T-SQL statement “Alter Login… With Name=…”will be successful:
I ran the statement after the the change took place in my Windows system;
ALTER Login [MXTLPT01\Dev01] WITH NAME = [MXTLPT01\DevUser01]
This time it works.
Wait! What about using PowerShell?
Check out the next blog post “PowerShell Working with SQL Logins Name”
Happy SQLAdmin!