0

I am explicitly granting select permissions on some specific tables to a database role and so when logged in with a SQL user having that role those tables are visible and qyeryable.

But when I remove the replication and recreate/reinitialize our replication to that DB/subscriber, and login to the server with that SQL user I am not able to access/view those tables in the subscriber database.

Any ideas what is happening here and what is the solution?

0

The issue was with my replication script which was passing drop as @pre_creation_cmd in sp_addarticle.

So, when the table was dropped all the permissions was dropped as well.

Once I changed it to delete it fixed the issue.

Thanks

-1

here you can find what happens when you reinizialize a subscription:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/frequently-asked-questions-for-replication-administrators?view=sql-server-ver15#what-happens-to-permissions-granted-in-a-subscription-database-if-a-subscription-is-reinitialized

What happens to permissions granted in a subscription database if a subscription is reinitialized?

By default, objects at the Subscriber are dropped and recreated when a subscription is reinitialized, which causes all granted permissions for those objects to be dropped. There are two ways to handle this:

  • Reapply the grants after the reinitialization using the techniques described in the previous section.
  • Specify that objects should not be dropped when the subscription is reinitialized. Prior to reinitialization, either:
    • Execute sp_changearticle or sp_changemergearticle. Specify a value of 'pre_creation_cmd' (sp_changearticle) or 'pre_creation_command' (sp_changemergearticle) for the parameter @property and a value of 'none', 'delete' or 'truncate' for the parameter @value.
    • In the Article Properties - dialog box in the Destination Object section, select a value of Keep existing object unchanged, Delete data. If article has a row filter, delete only data that matches the filter. or Truncate all data in the existing object for the option Action if name is in use. For more information on accessing this dialog box, see View and Modify Publication Properties.
2
  • 1
    Please don't post link only answers. They can become obsolete. Instead consider adding the solution as text and referencing what you included in your answer. Thanks. – John K. N. Feb 22 at 8:52
  • Done what you asked. But i can't rewrite the whole microsoft doc. – MBuschi Feb 23 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.