Fixing SQL Logins after Migration

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.

  1. Assign ownership of the database to a current user on the new server Alter authorization on database::[DATABASENAME] to [DOMAIN\USERNAME]
  2. Find the users that aren’t associated with their SQL Login with Exec sp_change_users_login ‘Report’
  3. 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.

Leave a Reply

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