September 9, 2011 at 8:30 pm
Hello Admins
SQL-Server 2008 R2
I am trying to add login (company\developers) to standby database. Since the DB is read only i am cant add the login
so i did this
RESTORE DATABASE test_logship WITH RECOVERY
Added the login (company\developers) as dbreader & dbowner
then made the DB back to logshipping mode
RESTORE DATABASE test_logship FROM DISK = 'D:\Backups\test_logship.bak' WITH MOVE
'test_logship' to 'e:\sql_data\test_logship.mdf', MOVE
'test_logship_log' to 'e:\sql_log\test_logship.ldf',
STANDBY = 'e:\standby\test_logship_rollbak.bak', REPLACE
but my login (company\developers) is missing the dbreader & dbowner permission for the DB test_logship after the DB refresh (restore command)
any ideas on how i can maintain the state of the permission even after DB refresh?
September 10, 2011 at 1:50 pm
as this looks like a windows login and therefore should not have been orphaned by restoring on a different server I can only presume the database backup did not contain these users or permissions.
---------------------------------------------------------------------
September 10, 2011 at 4:58 pm
if your source db had dbreader and dbowner roles for a login, those should come to the destination database as well. If the login is missing on destination server, it'd be a problem. you can create the login on the destination with the same sid that is present on the restored db.
btw if a login is already a db owner, we dont need to give dbreader role to it 😉
September 11, 2011 at 10:04 am
Run sp_help_revlogin on the primary instance. That will give you a script as output of all logins on the instance. Run that script on the standby instance to re-create the logins with all role mappings as they exist on the principal instance. Alternatively, you can copy specific portions of the script for individual logins that you are interested in.
Sp_help_revlogin is a master-db sproc and can be downloaded.
There is no need to recover the standby database and make it read-write, in order to recreate permissions from the principal.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 11, 2011 at 1:15 pm
There is no need to recover the standby database and make it read-write, in order to recreate permissions from the principal.
agreed. Any changes to database users made on the primary will automatically be copied over to the secondary when logshipping or restoring full backups.
---------------------------------------------------------------------
September 11, 2011 at 2:14 pm
sqlcool (9/9/2011)
Added the login (company\developers) as dbreader & dbowner
Ensure you make this change at the primary server and simply allow log shipping to take it's course. Also add the server login to the secondary server too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2011 at 4:01 pm
If you make a DB restore on the secondary server, everything will be over written.
Make the necessary changes on the primary & it will be replicated to the secondary.
Thank You,
Best Regards,
SQLBuddy
September 14, 2011 at 1:52 pm
Hello gurus,
I'm working on SQL SERVER 2008 R2. Created a log shipping routine with secondary database in standby mode. The LS process is working fine, no errors. However, the Server Login that I have mapped to my database on my primary server is not being created on the secondary server. I assume that the mapped database user is not there either, but I can't open the db because it's in standby mode. Is this contradictory to what you're saying or am I missing something? thanks
September 14, 2011 at 2:07 pm
SQL SERVER NOOB (9/14/2011)
Hello gurus,I'm working on SQL SERVER 2008 R2. Created a log shipping routine with secondary database in standby mode. The LS process is working fine, no errors. However, the Server Login that I have mapped to my database on my primary server is not being created on the secondary server. I assume that the mapped database user is not there either, but I can't open the db because it's in standby mode. Is this contradictory to what you're saying or am I missing something? thanks
The login will not be automatically created on the secondary server by the log-shipping process.
Run "sp_help_revlogin" on the primary server and isolate the portion in the output referencing the login you are interested in.
Run that portion on the secondary server.
That will create the login on the 2ndary server with all permission mappings to the db as in production.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 14, 2011 at 2:11 pm
SQL SERVER NOOB (9/14/2011)
I can't open the db because it's in standby mode.
You should be able to open and check your database. That's why we have the standby mode.
However, as rightly pointed out, logins are not shipped over to secondary, since logins are stored in master db.
September 14, 2011 at 6:18 pm
Server level objects are not log shipped. Only the Primary Db changes will be shipped.
Try to test it with a test db on a test server and by bringing back the standby db to R\W mode.
Thank You,
Best Regards,
SQLBuddy
September 15, 2011 at 8:27 am
thanks to all for the replies.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply