WilliamD avatar image
WilliamD asked

How can I best setup database permissions on a replication subscriber

I have a server that is replicating a large part of our database to secondary servers across the globe. This is done via transactional replication with the goal of supplying our offices with read-only access to our data. I am currently looking at the best way of allowing our in-house client software to access these secondary servers, but only allowing read-only access to the data. The best way being, to change the client software to check what server it connects to and then not allow write operations - this would require too much recoding (time constraints apply as usual!). We use application and database roles in *most* of the software, so applying a DENY to those would stop about 85% of the possibilities of writing to the database. However, the remaining 15% (mostly old programs that are being phased out) could still slip through and write to the secondary systems. I have considered the following: 1. DENY insert/update/delete on all tables - this won't work. I can't deny to all users and allow a subset (the replication users and some sysadmin types), as DENY overrides all GRANTs. 2. Database trigger monitoring all inserts/updates/deletes and allowing for some and refusing for others - overkill, would slow everything down on the secondary system, right? 3. Create a database snapshot each day. Would work, but we would like the subscriber systems to be "real-time" systems, so this would not be such a good solution. 4. Take the "risk" for the time being, informing the offices of read-only nature of the secondaries and get the client software rewritten over time. The writes that can be made against the secondary systems are not terrible, it would only really be updates and deletes, as the inserts would not work (we work with number ranging for ID generation that would not work on the secondary servers). So the worst someone could do, is change a status that would then be overwritten by the primary system / never be seen anywhere except for where the mistake was made. Does anyone have any ideas/thoughts on this? Have I missed a really obvious solution? Are any of the possibilities I mentioned a good idea? All systems are running SQL Server 2008 Enterprise Edition / Windows 2008R2 Enterprise Edition, so all possible features are at my disposal :) Would love to hear any input Thanks in advance.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Fatherjack avatar image
Fatherjack answered
Some thoughts, in no order: Can you use roles to group your replication accounts and your other accounts? you could then use deny on your majority and exclude the critical ones. Are you using the replication in one direction? Cant the replication simply over-write the changes at the remote locations? Users (I know from experience) slowly pick up on the fact that details get obliterated every 10 minutes and give up. How do these users connect to the data? If its via ODBC can you not specify in there that its read only in nature? If you used Log Shipping the secondary databases could be Read-Only thus avoiding any issues like this.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Certainly - looking into the "super" roles idea. As soon as you mentioned it it seemed obvious! :) That should cover most bases - the parts that slip through will learn, as you said, that data will disappear. I will wait a while before ticking this, in case any other ideas show up.
1 Like 1 ·
We looked at log-shipping, but Replication seems the "easiest" in terms of keeping a real-time system running at the secondary location. With log-shipping we would be playing around with 15 minute time slots and kicking connections to get thing sorted. That would be similar to the database snapshot scenario.
0 Likes 0 ·
yup, that's our downside - every hour all reports go offline for 2 minutes. Again however, the users have got used to this and its seems to work satisfactorily. Any of the other options any good? Spark any ideas?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.