?
Solved

sql replication removing permissions on database

Posted on 2014-01-31
2
Medium Priority
?
370 Views
Last Modified: 2014-01-31
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
Comment
Question by:jimmylew52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39824203
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
 
LVL 1

Author Closing Comment

by:jimmylew52
ID: 39825529
Thank You
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question