Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1806
  • Last Modified:

revlogin before or after restoring databases

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?
0
sqlserverdba
Asked:
sqlserverdba
  • 3
  • 2
4 Solutions
 
Chris MConsulting - Technology ServicesCommented:
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.
0
 
JohnSansomCommented:
Hi,

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?
0
 
sqlserverdbaAuthor Commented:
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Chris MConsulting - Technology ServicesCommented:
It's simple:
1. Restore database on new server.
2. Run results of rev_login procedure.

This is the recommended way of doing it.
0
 
JohnSansomCommented:
Hi,

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,

http://msdn.microsoft.com/en-us/library/ms174378.aspx

Hope this helps.

Regards,
John
0
 
Chris MConsulting - Technology ServicesCommented:
You optionally can modify your script to re-create the database mappings like they were before.
It all works.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now