Sql-server – Maintaining permissions on a replicated view after reinitialization

permissionsreplicationsql-server-2012view

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?

Best Answer

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

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

Sql-server – Set permissions on a view

Don't grant UPDATE rights on the view. Grant update rights on specific column within the view. Only grant the update rights on the columns which you want the user / application to be able to update.

Best Answer

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

Sql-server – Set permissions on a view

Related Question