February 24, 2010 at 3:11 pm
Hi Experts
I have a database (well many) which is log shipped to a warm backup on another server, and the secondary database is restored in standby/readonly mode. I want to move some of our reports to point at the readonly standby databases on the warm backup server to take some load off the live server.
The problem I have is: while the database user exists in the database, the user is orphaned from its login on the backup server. So, how can I re-map the database user to its login while the database is in readonly mode. Or, how I can temp take the DB out of readonly, re-map the login, then set the DB back to standy WITHOUT disrupting the log ship process?
Hope that makes sense.
Thanks for your help 🙂
February 24, 2010 at 3:16 pm
use sp_help_revlogin to generate details for the user on the primary. load the output from that into the secondary. That will bring the sid over and the user will no longer be orphaned. Drop the login first on the secondary.
---------------------------------------------------------------------
February 24, 2010 at 3:21 pm
Thank you. Will try that first thing and report on the outcome. Again, muchas gracias.
February 24, 2010 at 3:40 pm
Just to add to what George, said sp_help_revlogin is not included in SQL Server by default. This link may help you:
http://support.microsoft.com/kb/246133
Gethyn Elliswww.gethynellis.com
February 25, 2010 at 5:30 am
Thanks guys that worked a treat. I created the sp's needed for the sp_help_revlogin and was able to generate the logins to apply on the secondary server while the DBs were in standby/readonly mode. Happy days.
Some logins complained that the SID already existed, luckily I didnt need those, just a login for reports.
Thanks very much 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply