How to fix the users after SQL migration?

msSQL has system stored procedure called sp_change_users_login which can be used to report about broken user accounts and auto fix them.

After restoring a msSQL backup from one server on another server all user accounts will not be properly linked to the database. The sp_change_users_login stored procedure can help you find all users that are not linked and need to be fixed before they van be used again.

The command to use is: sp_change_users_login ‘report’
Executing this sp in ms SQL server management studio (Query Analyzer) will list all users that are not properly linked to the current database.

To fix these users run the same Stored Procedure with the ‘Auto_Fix’ parameter once for every user.

The command to use is: sp_change_users_login ‘Auto_Fix’, ‘[USERNAME]’
(replace [USERNAME] with the appropriate username)

To check if all users are properly linked after “auto_fixing” them, run the “report” again.

For more details about the sp_change_users_login see Microsoft Developer Network

Leave a Reply

Your email address will not be published. Required fields are marked *