November 21, 2006 at 8:44 am
I have a production db in which we send logs every hour to a read only reporting server. If you want a user to have access only to the reporting only read only db, how do you do this without giving the user access to production? You can't add the rights to the user on the reporting because its read only and if I remove access from production, wont the next log that is shipped no longer include access for that user in the sysuser table?
November 21, 2006 at 10:04 am
This is a case where you may need to look at another means such as transactional replication. The issues you cite are certainly show-stoppers for this type of read-only solution... One way to get around it is to give server role level rights (sysadmin) on the reporting server, but then it's not read-only, unfortunately.
K. Brian Kelley
@kbriankelley
November 21, 2006 at 10:10 am
Thats what I thought. Thanks for your response.
November 21, 2006 at 12:31 pm
In addition no user can be connected to the read only database while a log is trying to restore. It will cause log shipping to fail.
Log Shipping is not a good solution for Reporting.
November 22, 2006 at 6:48 am
SQL Server doesn't seem to mind orphaned users caused by restoring backups from one server to another server with different logins. You could take advantage of this by creating a SQL login on the production server, give it access to the log-shipped database and grant all the permissions you think it will need, then manually delete the login from the production master..sysxlogins table (i.e. don't use sp_droplogin, enable system table updates and use a "DELETE FROM sysxlogins WHERE SID=" statement. Very carefully.). Then create a SQL login with the same SID on the reporting server.
Without a login the user on the production system has no access but it would work on the reporting server. Manual updates to system tables are not a recommended practice, but this is what I would try if I was under intense pressure to solve the problem as you stated it.
November 22, 2006 at 7:15 am
Transactional replication is the way forward but rember the disadvantages:
More complex setup than log shipping,
More newtork traffic,
Complex failback after primary failure.
November 27, 2006 at 11:01 am
We're doing the same thing, but I'm restoring logs only once a day. I created a job on the primary server to change the password of the report username to an "unknown" password after the log files are restored onto the secondary. Then before the final log file is created I change it back to the "known" password so that it is passed along to the secondary again. (Files are created & copied every 30 minutes.) Now reports can only be generated on the secondary. This solution has been working for us for a couple of years now... maybe it can for you too.
November 27, 2006 at 11:18 am
Wouldn't your solution (unknown password on the primary) work if you didn't bother changing the password every day? Passwords are stored in the master database, so they wouldn't be part of the log-shipped data.
November 27, 2006 at 11:46 am
It seems to me that log shipping got out of synch once and the user account didn't work on the secondary because the password hadn't been changed back. Can't recall the exact details but I left the jobs to change the passwords and haven't had any problems since. I don't really want to test not changing it in production 🙂 Either way, changing the password is an easy workaround.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply