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'
Related articles:

MySQL search and replace across the whole database
How to protect and restrict access to a web directory using Apache web server
Installing Apache web server with PHP module, MSSQL conectivity and Ioncube loader

 

No comments yet

Back to articles list

This page was last modified on 2024-09-09 13:22:53