SQL Server Performance

Security on read-only database

Discussion in 'General DBA Questions' started by rklimes, Feb 8, 2008.

  1. rklimes New Member

    I have a log shipped database that is in read-only mode. I wanted to add a new login to be able to access this database but since it is read-only icant add access. Is there a way to accomplish this or do I have to take the database out of read-only to add access? And if so can I re-enable the read-only mode without having to restart log shipping?
    Thanks
  2. satya Moderator

    If you do so the log shipping will break and in order to continue you should RESTORE full backup again [;)], in this case you could perform such tasks with a scheduled job during a full backup restore on standby server, say periodically.
  3. rklimes New Member

    I am still unable to figure out how to add a login on my destination server to be able to access the standby/read-only database. When I restore the database in read-only, it has all the users that are on the live database but i have no way of associating those users with logins on my standby box. Any help would be great.
  4. satya Moderator

    If its a windows based login then first you will create on the master database and then grant permissions on that user database, in any case having a script is handy in this case.
    You must remove READONLY flag from that databse in order to update any of such values, in this case you should have latest backup overnight that will take care of such permission from source to destination server.
  5. rklimes New Member

    Since this is a log shipping destination database i cannot remove READONLY flag with
    ALTER DATABASE <database name>
    SET READ_ONLY
    because the database in in a "loading state". To get it out of "loading state" i need to use
    RESTORE DATABASE <database name> with RECOVERY
    After this I don't thin I can get it back to loading state to be able to restore transaction logs.
    If there is a way to do this I am unaware of it.

  6. satya Moderator

    STop log shipping until this is finished and stop before taking database backup, then start once it is over on secondary server.
  7. rklimes New Member

    thanks for the help but I found a solution. I used sp_help_revlogin SP to create script of login on primary server which includes SID, which was the piece i was missing to associate the login on secondary box the the user in the log shipped database.

    http://support.microsoft.com/kb/246133
  8. satya Moderator

    But in order to apply that script on secondary you should interrupt the log shipping.
  9. joserami New Member

    Actually running the sp_help_revlogin on the source server will provide a script he later will run on the secondary against the master database; since the database is log-shipped, the process will take care of the database mapping and security all by itself.

    There is no need to break log shipping
  10. Luis Martin Moderator

    You have noticed that this thread is from 2008, haven't you?
  11. Panchumarthi New Member

    We can first created the login and map the roles at the primary database level and then let the log shipping backup, copy and restore jobs run as per the schedules.

    After the jobs run successfully permissions will be flown down to Secondary database and we can verify them.

    Once we confirms that the login exists on secondary , now can go back and remove the login at the primary database level as we may not needed the users connected to the primary database.
  12. Luis Martin Moderator

    Panchumartihi welcome to the forums.
    This thread is 6 years old:)

Share This Page