April 30, 2008 at 7:42 pm
I have a transactional replication with central publisher and 10 subscribers. The replication runs pretty smooth. Now I want to add a new stored proc as an article to the existing subscription and then distribute it to the subscribers. The 10 subscribers are set to synchronise once a day at 4 pm.
I am able to add the new article using exec sp_addarticle Replication command. Is there a way that I can grant execute rights on that newly created SP to a particular user/role on that subscriber.
Whats the best way to do it.
April 30, 2008 at 10:12 pm
If the users/roles exist in both the publishing and the subscribing databases, you can get replication to copy the permissions for the stored procedures. The @schema_option parameter is the parameter you need to understand - if you set the "Replicate permissions" bit in this parameter, replication will replicate permissions for you.
I don't think that this setting is exposed in the GUI (for stored procs) but it is for tables. I initially assumed that it wasn't going to work but tried it and was quite pleasantly surprised to find that permissions were granted to users/roles.
If the users/roles don't exist in both databases, you might need to look at other options. Perhaps a DDL trigger might help. I haven't needed to code one of these so I would be guessing what is needed. Check out BOL or MSDN.
May 2, 2008 at 10:46 am
I have a transactional replication with central publisher and 10 subscribers. The replication runs pretty smooth. Now I want to add a new stored proc as an article to the existing subscription and then distribute it to the subscribers. The 10 subscribers are set to synchronise once a day at 4 pm.
I am able to add the new article using exec sp_addarticle Replication command. Is there a way that I can grant execute rights on that newly created SP to a particular user/role on that subscriber.
Transactional is usually continuous, but you suggest a once/day sync which smacks more of Snapshot
- please clarify what you are doing (if transactional will be up-to-date so why drop/resynch daily?)
adding an article to existing (running) pub won't get pushed out (unless DDL=1), so you could add another pub alongside [and blend together at some future quiet time].
as previous poster said, you can have pubs flow all permissions, but if you need more-specific control you should consider the sp_addpublication @post_snapshot_script='myscript.sql'
this works well but you can only have one script, so you may have to customise with
IF @@servername='sub1' BEGIN .. END else if @@servername='sub2' ...
HTH
Dick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply