ALTER USER WITH LOGIN throws error "Windows NT user or group not found"

Question

Attempting to deprecate the procedure sp_change_users_login as recommended by MS. I tried using:

ALTER USER UserName WITH LOGIN = UserName

as demonstrated here. This results in the error

Msg 15401, Level 16, State 2, Line 3458
Windows NT user or group 'UserName' not found. Check the name again.

UserName is both the user and the SQL login. Surely I am missing something simple but I cannot see what it is.

I had 3 names that failed and one does not have a login, but the other 2 do have logins. I can see the login and users in the Object Explorer. However if I open the login properties the user is not assigned to the target database. If I manually check the db box it works, but the point is that I am trying to clear up any orphaned users in my target database using SQL.

Answer 1

This error will happen if the login you are attempting to use does not exist.

Example:

CREATE LOGIN username WITH PASSWORD = '131fEWF$#@FR$#Vverf';
CREATE LOGIN username2 WITH PASSWORD = '131fEWF$#@FR$#Vverf';

USE tempdb;
CREATE USER username FOR LOGIN username;

ALTER USER username WITH LOGIN = username2; /*Successful*/

DROP LOGIN username;

ALTER USER username WITH LOGIN = username; /*Unsuccessful*/

You should check to ensure that the login was not removed prior to your attempt to run the ALTER USER command.