July 5, 2006 at 1:21 am
I am seeking advices to add users to the currently log shipped database. We use the log shipped database for some special read access processes. I would like to add an sql logon to this warm standby database. Of course, it is in read-only mode. I couldn't do so.
Then I have added the sql logon to the primary (log ship from) database. However, after I restored the database to the secondary (log ship to) database, the logon is broken. Usually I used the sp_change_users_login auto-fix to fix the logon. Again it's in read-only and I can't update this sql logon.
I have created the same logon name and password in both primary and secondary servers.
My question is that why the logon is broken after database restore? We are running sql 2000 with sp3. Any input will be appreciated. Thanks.
July 5, 2006 at 3:42 am
Hi Simon,
The reason the logon is broken is because the thing that determines whether a logon is broken or not is the security identfier (sid). When you created the same logon on the secondary server a new sid was generated.
What you need to do is copy the logons from your primary server to the secondary server. There's a specific DTS task that allows you to do this. I'm guessing that it's probably possible to do this without needing to restart log shipping.
Hope that helps,
July 6, 2006 at 12:43 pm
Hi:
DTS will help to transfer the logins, but not SID, for that you have to run the STore Procedure in text view in QA & then copy that TEXT results to the standy by server & run under QA. by doing so this will create same SID which is on Promary. please follow this link
http://support.microsoft.com/kb/246133/
I hope this will help.
July 7, 2006 at 8:42 am
If you have log-shipping in place, to maintain a warm stand-by, when should you implement the transfer of log-ins from the primary server to the secondary server? Can you wait 'til your primary server goes down or should you do it before? I apologize if this is a silly question.
Thanks...Chris
July 7, 2006 at 2:09 pm
Hi Chris,
ideally you want to transfer the logins prior to setting up log shipping because once the primary server goes down you'll have little chance of transferring them from the primary (since it's down).
If you don't have many logins then you can always manually create them on the secondary server but like I said, ideally you want everything to be in place so that when you bring your secondary online it's just a questions of pointing your application to the new server.
July 7, 2006 at 2:17 pm
Thanks, Karl.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply