I recently migrated a SQL Server 2008 R2 database to SQL Server 2014. We host with Rackspace, so I had them restore our databases from backup to the new server. I was left with database users that weren’t linked to SQL Server Users. Even after I added the users they were still not linked together and the system wouldn’t offer me the option. The following SQL allowed me to fix the problem.
- Assign ownership of the database to a current user on the new server Alter authorization on database::[DATABASENAME] to [DOMAIN\USERNAME]
- Find the users that aren’t associated with their SQL Login with Exec sp_change_users_login ‘Report’
- Autofix the logins to link the SQL Server Database User to the SQL Server Login User exec sp_change_users_Login ‘Auto_Fix’,’USERNAME’
The new database clearly won’t have the SQL Server Login Users until you add them so add them manually before you run the above routines.