视图的权限没有被复制 [英] permissions on the view are not getting replicated

问题描述

我正在使用 sql server 2012 和其中的事务复制.我试图复制一个在权限中附加了特定数据库角色的视图.这是我的视图复制脚本

im using sql server 2012 and transactional replication in it. im trying to replicate a view which has a specific database role attached in the permissions. this is my replications script for the view

:setvar SubscriberDB "ReplicationSubscriberDB"
:setvar SubscriberServerName "HYDHTC0131320D\MSSQLSERVER2"
:setvar PublisherDB "PublisherDB"
:setvar PublisherServerName "HYDHTC0131320D"
:setvar ReplicationAccount "myusername"
:setvar ReplicationAccountPassword "mypassword"

GO
:on error exit
GO

exec sp_addpublication @publication = N'DepartmentsView', @description = N'Transactional publication of database from Publisher ''$(PublisherServerName)''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',  @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO
exec sp_addpublication_snapshot @publication = N'DepartmentsView', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'$(ReplicationAccount)', @job_password = '$(ReplicationAccountPassword)', @publisher_security_mode = 1
GO


exec sp_addarticle @publication = N'DepartmentsView', @article = N'vwDepartments', @source_owner = N'dbo', @source_object = N'vwDepartments', @type = N'view schema only', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x48000001, @destination_table = N'vwDepartments', @destination_owner = N'dbo'
GO

EXEC sp_startpublication_snapshot @publication = N'DepartmentsView'
GO


use [$(PublisherDB)]
GO
exec sp_addsubscription @publication = N'DepartmentsView', @subscriber = N'$(SubscriberServerName)', @destination_db = N'$(SubscriberDB)', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
GO

exec sp_addpushsubscription_agent @publication = N'DepartmentsView', @subscriber = N'$(SubscriberServerName)', @subscriber_db = N'$(SubscriberDB)', @job_login = N'$(ReplicationAccount)', @job_password = '$(ReplicationAccountPassword)', @subscriber_security_mode = 1, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 6, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20120607, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

复制后，视图正在创建，但角色没有附加到视图的权限.此角色同时存在于发布者和订阅者数据库中.并且此角色被分配给视图中使用的表，并且该表存在于两个数据库中.

after replicating, the view is getting created but the role is not getting attached to permissions of the view. this role is present in both publisher and subscriber databases. and this roles is assigned to the table used in the view and the table exists on both the databases.

我尝试将 schema_option 设置为 0x48000001，即

i tried setting the schema_option to 0x48000001 which is

1. 生成对象创建脚本(CREATE TABLE、CREATE PROCEDURE 等).此值是存储过程文章的默认值.
2. 创建订阅者上尚不存在的任何架构.
3. 复制权限.

我也看过这篇文章，http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/a4fa2d32-00ea-47a7-9276-52764fab72ae/ 上面说的 schema_option 值应该可以工作.或者我们必须在 sp_addpublication 中使用 @post_snapshot_script.

i have also gone through this post, http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/a4fa2d32-00ea-47a7-9276-52764fab72ae/ which says above schema_option value should work. or we have to use @post_snapshot_script in sp_addpublication.

我尝试使用@post_snapshot_script 并且它正在工作，但我不想使用这种方法.

i tried with @post_snapshot_script and it is working, but i dont want to use this approach.

请告诉我如何解决这个问题.

please let me know how to solve this problem.

提前致谢.

推荐答案

这是 SQL Server 2012 中的一个已知问题.发生此问题的原因是快照代理中的错误.在 Microsoft 发布针对此问题的修复程序之前，目前有一个解决方法:1. 打开快照文件夹.2. 通过添加所需的权限，为您的视图编辑 .sch 文件.

This is a known issue in SQL Server 2012. This issue happens because of a bug in the snapshot agent. There is a work around for now till Microsoft release a fix for this issue: 1. Open the snapshot folder. 2. Edit the .sch file for your view by adding the required permissions.

订阅者可以在此之后获得正确的快照.

Subscribers can get the correct snapshot after that.

这篇关于视图的权限没有被复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！