Fix MS-SQL Orphaned user

Fix the orphaned user supposed to be quit easy, however I kept encountering the problem. The following script is how I used to fix the orphaned user

EXEC sp_change_users_login 'REPORT'

after running the above script, you will get a list of orphaned users

UserName UserSID
—- ——————
Annie 0xA5B5548F3DC81D4693E769631629CE1D

You then run the following command in your query window to fix the above orphaned users

  EXEC sp_change_users_login 'UPDATE_ONE','Annie','Annie'

However, you may receive another error e.g. Terminating this procedure. The Login name ‘XXX’ is absent or invalid.  It’s really annoying and making me frustrated.  Since you do not have a login created for that user, you should use ‘Auto_Fix’ option (also make sure that you are connected to your database and use sysadmin account):

exec sp_change_users_login 'Auto_Fix', 'XXX', NULL, 'password'

Or, you can create a login first and then use ‘update_one’

Advertisements