Posted by: Denny Cherry
Back To Basics, sp_change_users_login, SQL
The sp_change_users_login procedure has a specific purpose. It’s used to identify and correct users within a database which do not have a corresponding logins.
You can specify the value of Report for the @Action input parameter to see any users which do not have a corresponding login. This only needs to be done when you are restoring a database from one server to another, and the logins on each server were created with seperate SIDs. An example of when this would happen is when you are restoring a database from Production to QA.
After identifying the users which are not synced to a login, use the update_one value for the @Action parameter and with the username of the user as the value for the @UserNamePattern input parameter, and the login name as the value of the @LoginName input parameter. If the login doesn’t exist you can pass the password to the procedure for the @Password input parameter and the login will be created for you and mapped to the user.