Fixing orphaned database users when migrating MSSQL database to different server

When database from one MSSQL server is migrated to another using detach -> copy -> attach you get into a funny situation: no server logins are created for database users and you cannot login to the newly imported database. To fix this use sp_change_users_login procedure.

First run is to detect which are the orphaned users:

sp_change_users_login report

Then for each of the reported users you have three options:

1. If login with the same name already exists and you want to map the user to it type this:

sp_change_users_login 'auto_fix', 'orphanuser', null

2. Create login with the same name as the user and password 'password':

sp_change_users_login 'auto_fix', 'orphanuser', null, 'password'

3. Map the user to some other login:

sp_change_users_login 'update_one', 'orphanuser', 'existinglogin'

 

No comments yet

Back to articles list

This page was last modified on 2018-12-09 01:30:58