Back To Basics: How do I use sp_change_users_login?

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.



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?