Symptoms: You are using SQL Server replication (transactional/merge/snapshot) subscribers for transactions. You have provided users some object level permissions on specific objects (tables/stored procedures etc.) at subscriber database. After applying the snapshot users complaint about missing permissions on subscriber database.
Cause: In SQL server replication, while applying snapshot default behavior is to drop existing objects from subscriber database and recreate those objects. Due to this users having any permissions on subscriber database objects loses their permissions.
Workaround: You should always use a post snapshot script to apply/fix object level permissions at subscriber database. This script could be executed manually on subscriber. To eliminate manual intervention, you can mention same script in publication properties in snapshot tab. Please ensure to place script in a shared path accessible from all subscribers or put the script in snapshot folder. Script path need to be provided in ‘after applying the snapshot, execute this script’ section of replication tab.
Hope this information helps you!!
Happy learning!!