Manage logins and users to log shipping secondary server

Question

Manage logins and users to log shipping secondary server

tnk

SSCrazy

Points: 2613
More actions
May 10, 2011 at 1:42 am

#239331

I want to give some users that have limited access in the production database, full read only on all objects in the standby secondary server.
I tried to update their roles but am being denied because the database is read-only, which I am aware of because I put it that way. What's the workaround?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

there isn't directly, but what you can do is create another account on prod with the rights required - this will migrate to log shipping ( assuming you're moving logins as well - otherwise create logins at both ends ) Once done hold different passwords so that the login can't be used from the log shipped server on your production box.

btw. giving users access to a log shipped database breaks the licensing if you have not licensed your log shipped server because it's in standby.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

tnk SSCrazy Points: 2613 More actions · Answer 2

I am not currently moving logins but probably should. It would lessen the amount of configuration we would need to do in the event of an actual disaster.

I'll see how the different login thing goes over. THanks for the tip.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

When your logshipping fail and you want to up the secondary node then you will pass this statement on the secondary node

Restore database [your database name] with recovery;

If database have locks then pass this statement

Alter database [your Database Name] set offline with rollback immediate;

Alter database [your Database Name] set online

Restore database [your Database Name] with recovery;

if you have Tail-Log backup from the primary server then apply it on the secondary node with recovery mode then ,there is no need of that script which one mentioned above,after this then you can map your logins because database is now READWRITE mode

My Blog
www.aureus-salah.com

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

the obvious danger of adding all those certifications after your signature is you put yourself up for a fall.. I can only assume you've never used a log shipped standby server as the correct method to bring the server on-line is to " restore log dbname with recovery ".

most alter database commands cannot be applied to a standby server too.

If I were you I'd drop all those certifications as it's considerd poor etiquette. 🙂

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

Quite surpirse to find Alter database command for log shipping standby databases.

In case you have added new logins in the primary databases then either use SSIS or script to move the logins to standby server with same password and permisssion. As mentioned the DBs are in readonly mode you will not be able to make these logins to access the DBs but just create them making Master as a default DB.

Once you have recovered the DBs on standby .. anytime if disaster, then run the script to change the default DB and permissions.

"More Green More Oxygen !! Plant a tree today"

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

the obvious danger of adding all those certifications after your signature is you put yourself up for a fall.. I can only assume you've never used a log shipped standby server as the correct method to bring the server on-line is to " restore log dbname with recovery ".
most alter database commands cannot be applied to a standby server too.
If I were you I'd drop all those certifications as it's considerd poor etiquette. 🙂

I mentioned already when you will apply these statements,take it easy,Tell me if primary node fail then how you will up the Secondary Node

I am not currently moving logins but probably should. It would lessen the amount of configuration we would need to do in the event of an actual disaster.
I'll see how the different login thing goes over. THanks for the tip.

check this link,when and what you can do with logshipping

http://aureus-salah.com/2011/05/10/sql-server-logshipping-issue/#more-1250

My Blog
www.aureus-salah.com