1

When we were on SQL Server 2005, there was some way that I was able to keep custom permissions on a view at the subscriber.

Now that I'm on SQL Server 2012, there doesn't seem to be a way to do that.

In the article properties of a table, there is the "Action if name is in use" property "Truncate all data in the existing object" which will keep any permissions on the table.

However, views do not have this option. It is either "Keep existing object unchanged" or "Drop existing object and create a new one." If I update my view, I want the changes to be reflected in the replicated view, but I don't want my custom permissions to disappear.

Am I looking at starting a post-reinitialization script for this purpose or is there something I'm missing?

Improve this question

2 Answers 2

Reset to default
1

I ended up using a post-initialization script to grant this permission at the subscriber and it worked great.

Improve this answer
0

This is a known issue with SQL Server 2012. It used to work in previous versions. The Microsoft Connect item can be found here: https://connect.microsoft.com/SQLServer/feedback/details/769502/permissions-not-replicating-for-certain-object-types-when-using-a-sql-server-2012-distributor

We first noticed it on the MSDN Forums here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/912c282b-55ba-4aec-a68a-b5b7fa78a565/permissions-on-the-view-are-not-getting-replicated

Microsoft indicates that the issue was fixed in a service pack but I am unable to locate any release notes stating this. You may want to try applying the latest service pack to the Publisher/Distributor and Subscriber(s) and see if that helps.

If not, the current workaround is to post the GRANT statements using a post snapshot script or on-demand using sp_addscriptexec.

Improve this answer
1
  • This is not exactly my issue. The permissions replicate fine. What I need to do is maintain different permissions on the view at the subscriber. I can do this, but as soon as I reinitialize, I lose the custom permissions at the subscriber only to be replaced by the permissions from the distributor. I understand that "truncate all data in the existing object" doesn't exactly make sense for a view, but there must have been something like this on SQL 2005 as I didn't have this problem on that version.
    – Bendy22
    Commented Mar 24, 2015 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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