New Sql Login on STANDBY Server

Question

Hello,

We have set up a logshipping scenairo on 2 Sql Server 2005 machines. The secondary database is in STANDBY mode. We want to use this secondary server for reporting purposes, as the report viewers will query this STANDBY database according to their given execute rights. So we need multiple users on this secondary server, having different execute rights on the STANDBY database.

The problem is, after seting up log shipping, we can't grant the necessary permissions to the standby database as it is read-only.

Do you have any suggestions ?

Thanks, Umut

Answer 1

Sadly, you need to look into something other than log shipping.

Log shipping works by keeping up-to-date copies of your logs in a 'continually' restoring/recovery state on the secondary server. As such, the database there is never actually 'active' or live - as it's always just applying more and more logs and waiting for the command that will make it go active.

In other words, log shipping is ONLY for high-availability - it does NOT support duplication of your data in USABLE form.

To learn more about what purpose logging serves, check out this video: http://www.sqlservervideos.com/video/logging-essentials/

And if you really need a secondary server for reporting purposes, then I'd suggest using something like transactional replication. (It's NOT suitable for high-availability solutions because of some of the schema changes and limitations you'd have to make... but it does work well as a way to 'publish' multiple copies of your database to different servers/locations for reporting purposes.)

Answer 2

For some reason, till we setup a better reporting system, we need to use the Standby server for reporting purposes. And there is a way to create new users and give necessary sp execute rights to that user on the standby server. The simple solution is, to create a login on the primary server and Select its SID from master table. Then with "sp_addlogin" create the same login with the same SID on the Standby database. Then, on the primary server give required rights to the user on the database. With the restored transaction logs, the execute rights will ship to standby server. Passwords don't need to be the same on two servers, so the standby report viewer user can't access the primary server with the same credentials.