Microsoft SQL Server Database Migration: Typical Issues I

Database migrations or tasks related to data replication in other locations, such as backup servers or disaster prevention centers, can cause a lot of work per se, and are also not exempt from small details that expand development times and maintenance of these processes.

An issue that could be typical would be the apparition of "Orphan users". This may occur when the user login in the database is not linked to a corresponding login in the SQL Server instance. This can happen during database restores or when the login is deleted or renamed. In this context, probably it happens when the Database is restored.

The first attempt is usually to map the user in which we have the problem:

And we'll probably obtain this message:

Microsoft SQL Server provides an easy and powerful solution for that, it's called "auto_fix option" and consists on executing sp_change_users_login stored procedure, that automatically fixes these "Orphan users" and synchronizes login information between the database and server. The usage an syntax are as follows:

USE database_name;
EXEC sp_change_users_login 'auto_fix', 'username';

Replace database_name with the name of our database where we have this issue and replace 'username' with the name of the orphaned user you want to fix. When we execute this sentence, SQL Server attempts to map the user to an existing login by searching for a login with the same name. If found, it updates the user's SID (Security Identifier) to match the login's SID, and therefore the problem will be resolved.

The principal benefits we can obtain with this type of solution are the automation and batch processing, so we can execute the statement for several orphan users in a single procedure, planning a job (i.e.), etc. saving time and work compared to manual mapping of users to logins.

Finally, in the context of the security and permissions, we have to ensure that the user executing the statement must have appropriate permissions to execute the sp_change_users_login
stored procedure and update user information, like sa user. And it's important to ensure that the mapping is correct and it's necessary verifying all security requirements of the fixed users, validating that these orphaned user are linked to the identified login before executing the statement.

Hope this helps ;)
David.