Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.
EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'
- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.
AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction
