x

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.

more ▼

asked May 24, 2011 at 01:05 AM in Default

avatar image

WilliamD
26.2k 18 38 48

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered May 24, 2011 at 01:20 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

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.

May 24, 2011 at 01:30 AM WilliamD

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?

May 24, 2011 at 01:36 AM Fatherjack ♦♦

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.

May 24, 2011 at 02:30 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2219
x379
x218

asked: May 24, 2011 at 01:05 AM

Seen: 2918 times

Last Updated: May 24, 2011 at 01:05 AM

Copyright 2018 Redgate Software. Privacy Policy