I'm backuping and restoring databases accross two sql server (v12.0.2000).
But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent in the one that was restored. I expected it to be backuped to the bak file !
Is there any special manipulation for a login to be backuped in order to be easily restored on another server ?
I could not go on maintaining various script to restore the database on one hand and to recreate the logins on the other hand.
How could I create a consistent backup file that could easily be restored ?
Best Answer
The above issue which you are facing is commonly known as the case for Orphan users:
What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.
On how to fix this you can refer Fixing Orphaned Users
Or as an alternative you can also make use of SP_help_revlogin
1) Run the sp_help_revlogins against the MASTER on SOURCE SERVER, and get the script of "creating logins".
2) During the restoring the backup to the DESTINATION SERVER, you can run the "creating logins" script against the MASTER database on the DESTINATION SERVER.
3) After the restoration DONE, please
EXEC sp_change_users_login 'REPORT'against the RESTORED database.