• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

sql replication removing permissions on database

We are using transactional replication to replicate 3 tables from a sql 2008 database to a sql2012 database. Replication is is removing permissions from the sql2012 database.

I thought it was the snap shot at initiation that was causing it but the log shows the snapshot was delivered at 6:00 PM and the permissions were altered some time after 10:00 PM.

Any ideas on what is happening and how to stop it?
0
jimmylew52
Asked:
jimmylew52
1 Solution
 
lcohanDatabase AnalystCommented:
In my opinion that subscription was initialized therefor the permissions were lost. Please see notes about permissions in replicated databases and as you can see the "Replication" itself (other than subscription initialization) won't drop your granted permissions on destination (subscriber) database.

http://technet.microsoft.com/en-us/library/ms151740.aspx


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:
"
0
 
jimmylew52Author Commented:
Thank You
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now