Many problems with materialization are due to incorrect or missing permissions in primary or replicate databases.
Verify that the login accounts for the user creating a subscription meet the following requirements:
The user’s login name and password are the same at the replicate Replication Server, the primary Replication Server, and the primary data server.
The user has been added to the primary database.
The user has select permission on the primary table.
The user has execute permission on the rs_marker stored procedure.
This requirement applies to a configuration that uses RepAgent. If you are using a Replication Agent for a foreign data server, you may want to check your Replication Agent documentation for a similar requirement
The default installation scripts grant execute permission on rs_marker to “public,” so this should not be an issue unless you did not use the installation scripts.
The user has at least create object permission at the replicate Replication Server.
The user has at least primary subscribe permission at the primary Replication Server.
If you cannot give the user the required accounts and permissions, drop the subscription without purge and have a different user create it.
Make sure that the maintenance user for the replicate database has update, delete, insert, and select permission on the replicate table. Lack of update permission causes errors in the replicate database when the DSI attempts to apply transactions in the materialization queue.
Depending on error action assignments, some transactions may be rejected and recorded in the exceptions log.
