Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was hoping to get some help with a SQL Server 2012 transactional replication scenario - or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions to a particular table (like granting SELECT or INSERT) on the publisher in a transactional replication configuration, would that change the schema such that subscribers would need to pull from a new snapshot?

My concern is potential down-time with such a change where the subscriber would effectively be offline while the new snapshot is created. Ideally I could just grant new permissions to a particular user to a replicated article either on the publisher or subscriber without making things reset. I'm not concerned about the updated permissions being replicated from the publisher to the subscriber (in fact, ideally they wouldn't). Thanks in advance!

share|improve this question

1 Answer 1

up vote 4 down vote accepted

If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied.

If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the permissions will not be replicated on the fly, unless you generate a new snapshot and mark the Subscriber for reinitialization. Note that the article property Copy permissions must be set to true.

What is typically done is if you need to change a user's permissions to a particular table AFTER a Subscriber has been initialized, and you need the permissions to be present at the Subscriber without reinitializing the Subscriber, the script to grant permissions are posted to the Subscriber using sp_addscriptexec.

I have a post detailing sp_addscriptexec located here at Executing scripts with sp_addscriptexec.

share|improve this answer
3  
Excellent information! This is exactly what I was looking for but couldn't find through searching the web. Thank you very much for your help. –  Chris Feb 17 at 17:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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