Security on read-only database | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Security on read-only databaseI 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?
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.
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.
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.
Since this is a log shipping destination database i cannot remove READONLY flag with
ALTER DATABASE <database name>
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.
STop log shipping until this is finished and stop before taking database backup, then start once it is over on secondary server.
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
But in order to apply that script on secondary you should interrupt the log shipping.
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
You have noticed that this thread is from 2008, haven’t you?
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.
Panchumartihi welcome to the forums.
This thread is 6 years old