3

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 ?

Improve this question
1

2 Answers 2

Reset to default
2

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.

Improve this answer
1

Server Logins allow you to connect to the SQL Server. Logins are stored in the [master] database. Each Login is assigned a hexadecimal SID. Windows Authentication Logins get their SID from the Active Directory so the SID for a Login is same across servers in the network. SQL Authentication Logins get a SID generated by the SQL Server so the SID will be different on each server.

Database Users allow the Logins to use a database. Users are stored within the database itself. When a database User is created for a Login, the SID is passed on to the User. This common SID acts as a mapping between them.

When you backup and restore the database to a different server, the Users go with the database, but the Logins don't because they are in [master]. As Windows Authentication Logins have the same SID on all SQL Servers within a network, the Database Users in the restored database will find the same SID as the Login and thus map correctly. The SQL Authentication Logins have different SID on servers so the Database Users will not map correctly. Such Users without a corresponding Login are called Orphan Users.

To move Logins/Databases correctly, they have to be scripted out.

You can read this blog which talks about AlwaysOn but applies to regular servers too. https://aalamrangi.wordpress.com/2015/02/09/avoid-orphan-users-in-alwayson/

Improve this answer
1
  • @Aalamjet Rangi, thanks for your input. I tried to go with domain user to prevent SID mismatch. Unfortunately, I cannot login using userId and password in connection strings... and I have to go with impersonation in my powershell scripts which is REALLY not straightforward. If you have any advices you are welcome.
    – Koresh
    Commented Jun 17, 2015 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.