revlogin before or after restoring databases

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-06-27
we are restoring databases to another server. If we execute the output of sp_help_revlogin on  destination server first and then restore databases on destination server, will it reduce orphaned users?
or is it the same as restoring databases and then transferring logins later?
are there any pros and cons of these two methods?
Question by:sqlserverdba
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 12

Expert Comment

by:Chris M
ID: 26290728
It's best to do the restoration first then execute the output of the procedure.
The reason is that some of the batches may break if the destination databases being referred to are not there.
On the contrary, if you execute the output of the procedure after restoration, there's no problem at all.

Either way, orphaned logins will always come with the database(s) being restored.

Accepted Solution

JohnSansom earned 500 total points
ID: 26291185

There is no point in running the System Stored Procedure sp_help_revlogin until the database has been restored on the target server.

This is becuase the SID, the unique identifier for the SQL Server Login's on the target server, need to map to the Database User SID's on the restored database in order work. Therefore the SID's on the source database server become irrelevant.

Make sense?

Author Comment

ID: 26293008
so which is the best way to fix the orphan users?  the change users login procedure with autofix or update?
i am restoring the same backup as different databases with different names, so was just wondering if it makes any difference or any problems?
is the procedure still the same?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 12

Assisted Solution

by:Chris M
Chris M earned 500 total points
ID: 26293239
It's simple:
1. Restore database on new server.
2. Run results of rev_login procedure.

This is the recommended way of doing it.

Assisted Solution

JohnSansom earned 500 total points
ID: 26293304

I have just realised that I have been talking cross purposes and so to clarify if I may:

You should first run sp_help_revlogin as the source server in order to script out the SQL Server Logins you require.

Then Add these to the target server and then restore the database to the target server.

Once restore use the sp_change_users_login stored procedure to fix any orphaned users that may exist.

AUTOFIX can only map LOGINS to USERS that have the same name and so may not fix all Database Users. This is where the UPDATE option comes in, allowing you to specifiy exactly which SQL Sertver Login should map to which Database User.

Take a look at the Books Online documentation for usage examples and insutrctions,


Hope this helps.

LVL 12

Assisted Solution

by:Chris M
Chris M earned 500 total points
ID: 26293337
You optionally can modify your script to re-create the database mappings like they were before.
It all works.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question