SQL Server Performance

Adding Users to Log Shipped DB

Discussion in 'SQL Server Log Shipping' started by smlau, Jul 3, 2006.

  1. smlau New Member

    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.

    Any suggestion that I can accomplish this? Thanks.
  2. SQLDBcontrol New Member

    Hi,

    Unfortunately this is not possible. As you rightly point out, the database is in read-only mode so you would need to bring the database online in order to add a user. However, if you did that, then you'd break log shipping.

    You'd need to add the user to the primary database so that the user gets shipped to the secondary database. Of course, then that user would have access to the primary and secondary databases.




    quote:Originally posted by smlau

    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.

    Any suggestion that I can accomplish this? Thanks.


    Karl Grambow

    www.sqldbcontrol.com
  3. smlau New Member

    Thanks Karl. 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.
  4. SQLDBcontrol New Member

    Hi,

    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,


    Karl Grambow

    www.sqldbcontrol.com

Share This Page