Post reply

Replication deletes permissions

  • November 11, 2006 at 12:59 pm

    #91934

    I am resetting a replication that we have used for years. We are a state Legislature so the data gets cleared every two years. When I started the replication this year every time the process updates the subscription, all the permissions are wiped out. We've never had this problem before [that I can remember]. Any assistance appreciated.

    I'm using SQL2000 clustered to another SQL2000 server.

    Bob

  • November 13, 2006 at 12:07 pm

    #671417

    Sorry, I seem to have misdiagnosed the problem. The permissions were not staying when set in the subscriber database. I deleted all tables and recreated them, setting the permissions with script at the same time and they seem to be holding.

    Thanks for having this forum available and for those who puzzled over my strange problem.

    Bob

  • December 6, 2006 at 2:32 pm

    #676425

    I'm new to replication.

    I'm trying to set up a snapshot replication that will run once a day for our web page.  I am trying to set up a push subscription that recreates the tables and would like to grant select permissions on them.

    Where do you put a script that will grant the permissions for the tables in the subscriber database? 

    I've come up with a script to grant the select permissions

    sp_msforeachtable "GRANT SELECT ON ? TO public"

    but I'm not sure how or where to add this in to the replication. 

    I know I could set up a job on the subscriber to run after I know the Push has occurred but is there a better way to do this?

  • December 6, 2006 at 6:53 pm

    #676494

    Three ways you can do...

    1. Open the Publication property click on snapshot tap... at the bottom you see option called additional scripts before/After you can add your add script file there..

    Replication Monitor/Publisher/Publicationname/preperties/Snapshot

    2. You can run your script on Publish which it copy that to subscriber.

    3. You can run the your script on subscriber as sql job and schedule it to run after snapshot...

     

     

    MohammedU
    Microsoft SQL Server MVP

  • December 7, 2006 at 9:29 am

    #676666

    The first option was exactly what I needed.  Thanks!

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply