I was hoping to get some help with a SQL Server 2012 transactional replication scenario – or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions to a particular table (like granting SELECT or INSERT) on the publisher in a transactional replication configuration, would that change the schema such that subscribers would need to pull from a new snapshot?
My concern is potential down-time with such a change where the subscriber would effectively be offline while the new snapshot is created. Ideally I could just grant new permissions to a particular user to a replicated article either on the publisher or subscriber without making things reset. I'm not concerned about the updated permissions being replicated from the publisher to the subscriber (in fact, ideally they wouldn't). Thanks in advance!
Best Answer
If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied.
If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the permissions will not be replicated on the fly, unless you generate a new snapshot and mark the Subscriber for reinitialization. Note that the article property Copy permissions must be set to true.
What is typically done is if you need to change a user's permissions to a particular table AFTER a Subscriber has been initialized, and you need the permissions to be present at the Subscriber without reinitializing the Subscriber, the script to grant permissions are posted to the Subscriber using sp_addscriptexec.
I have a post detailing sp_addscriptexec located here at Executing scripts with sp_addscriptexec.