August 31, 2007 at 11:40 pm
Hello,
I have a user called 'Reportuser' in the primary DB server. I created the same user in the reporting server master db.
When I restore my DB in Standby/Readonly state to the reporting server, the 'ReportUser' stop functioning.
After the restore from primary server to Reporting server, the user 'reportuser' losses the mapping and is not associated with any DBs. When I try to add I get a msg stating that the user cannot be modified since the DB is in Stanby/Readonly mode.
Any suggestions?
September 3, 2007 at 1:40 am
Hi Ammad,
The Reportuser user in the restored database has been orphaned. This link should tell you all you need to do to fix it:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
Cath
September 4, 2007 at 12:07 am
The logins are always stored in the Master database irrespective of the database and the SID of the Reportuser should be different. That should have caused the issue.
September 4, 2007 at 10:50 am
I've run across the same problem with Log Shipping, but sp_help_revlogin makes it easy. Just create the procedure on the primary database server (in master) and run it in Query Analyzer. Then copy the results and paste into a QA window that is connected to the Secondary database server. Next delete all user accounts except the one you want and run it. This will match up the logins with the user accounts. You can find the procedure here.
September 4, 2007 at 3:46 pm
if the SQL Server logins already exists on the destination server you should use
sp_change_users_login 'Report' to verify which are orphaned
and sp_change_users_login 'UPDATE_ONE', 'user','user' to remap to
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply