November 18, 2008 at 12:47 pm
Guys,
Snapshot replication works great except one caveat: the replicated data (tables) do not retain original permissions... Is there a way to configure replication so that the permissions are automatically transferred over? Or the only way is to run script setting permissions afterwards?
Thanks in advance!
November 18, 2008 at 7:55 pm
Replication definitely supports what you need.
If you are using the replication stored procs, sp_addarticle has a parameter called @schema_option. This is a bit mask of the options you wish to use for a particular article with one of the bits meaning that permissions should be replicated.
If you are using the GUI, you need to set the article properties to do the equivalent. This is not obvious to the casual user because you need to click on a button to find it. The GUI allows you to apply this property to all articles if you need or you can do it for individual article.
HOWEVER, from memory, this is not available in the GUI when created snapshot publications (unfortunately, I am at a customer site and cannot confirm the details).
For this option to work, you must ensure that the database users exist in the subscribing database. If the users do not exist, replication will fail when it attempts to run the GRANT statement. Unfortunately, replication is not able to replicate database users for you.
November 19, 2008 at 6:36 am
Great! Thank YOU!
November 20, 2008 at 8:34 am
If you have custom permissions at the subscriber end you could also create a post-snapshot script that will apply the permissions for you. In the GUI, specify the post snapshot script on the "Snapshot" page of the publication properties in the section at the bottom which says "After applying the snapshot, execute this script".
November 20, 2008 at 4:39 pm
Thanks alot!!!
Best wishes!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply