需要学习如何在SQL Server数据库上设置适当的权限以允许通过Sync Framework 2.1进行同步 [英] Need to learn how to set appropriate permissions on SQL Server database to allow Synchronisation through Sync Framework 2.1

问题描述

SQL Server不是我的强项，当我经过基本的创建表"，从表中选择"等内容时，我开始迷路.

SQL Server is not my strong point and I start to get lost when going past the basic Create Table, Select from Table etc.

我正在尝试建立一个数据库同步方案，其中一个Microsoft SQLCompact 3.5数据库通过代理与SQL 2008 R2数据库进行同步.它正在通过Microsoft Sync Framework 2.1进行同步.

I am attempting to set up a database synchronisation scenario with an Microsoft SQLCompact 3.5 database being Synced through a Proxy to and SQL 2008 R2 database. It is being synced through the Microsoft Sync Framework 2.1.

当用户帐户是数据库的db_owner时，我已通过代理进行了所有设置并正常工作.

I got everything set up and working fine through the proxy when the user account is the db_owner of the database.

当然，现在这需要锁定-因此，我一直在尝试将权限限制为同步所需的最低限度.

Of course this now needs to be locked down - so I have been trying to restrict the permissions to the bare minimum required for Synchronisation.

根据Microsoft文章，我需要执行以下操作...

According to the Microsoft articles, I need to do the following...

使用最小权限原则.授予的权限不要超过执行特定任务所需的权限.例如，不要为仅下载同步中涉及的服务器数据库表授予INSERT权限.同步操作需要以下权限:

Use the principle of least permission. Do not grant more permissions than are required to perform a specific task. For example, do not grant INSERT permissions for server database tables that are involved in download-only synchronization. Synchronization operations require the following permissions:

对Sync Framework用于读取和写入元数据表和基表的所有存储过程的执行权限.

EXECUTE permissions on all the stored procedures that Sync Framework uses to read and write to metadata tables and base tables.

对元数据表和将在同步会话期间更新的任何基本表的SELECT，INSERT，UPDATE和DELETE权限.

SELECT, INSERT, UPDATE, and DELETE permissions for metadata tables and any base tables that will be updated during a synchronization session.

在设置使用SqlSyncProvider的SQL Server数据库时，请注意以下权限要求:

When you provision SQL Server databases that use SqlSyncProvider, be aware of the following permissions requirements for provisioning:

具有创建表权限，以允许创建元数据表:scope_info和scope_config，以及为每个基本表创建的跟踪表.

CREATE TABLE permissions to enable creation of the metadata tables: scope_info and scope_config, and the tracking tables that are created for each base table.

ALTER TABLE将触发器添加到基表.

ALTER TABLE to add triggers to the base tables.

创建过程权限以创建Sync Framework所需的过程.

CREATE PROCEDURE permissions to create the procedures that Sync Framework requires.

scope_info和scope_config表的SELECT和INSERT权限.

SELECT and INSERT permissions for scope_info and scope_config tables.

对基表的选择权限.

我允许Visual Studio 2010中的向导为我创建Sync数据库和代理.

I allowed the wizards in Visual Studio 2010 to create the Sync database and proxy for me.

如此-我无法在SQL Server数据库中找到scope_info和scope_config表，也无法找到元数据表，因此无法在这些表上设置权限.另外-我在哪里可以找到同步框架试图使用的存储过程-我已经找到了，但是找不到.

As such - I am unable to find the scope_info and scope_config tables in SQL Server databases, and I am also unable to find the metadata tables so cannot set permissions on these tables. Also - where would I find the stored procedures that the Synchronisation framework is trying to use - I have looked but cannto find them.

在哪里可以找到这些?如何设置适当的权限?

Where would I find these and how would I go about setting the appropriate permissions?

我已授予SQL Server数据库的数据读取器和数据写入器，插入，更新，删除和选择以及执行权限，但同步失败.我还为用户授予了数据库的创建表"，创建过程"和更改"权限，但仍然失败.

I have granted datareader and datawriter, Insert, Update, Delete and Select as well as Execute permissions on the SQL Server database but the sync fails. I have also granted Create Table, Create Procedure and ALTER permissions on the database for the user as well- but still it fails.

如果我为用户启用了db_owner角色-它会起作用.

If i enable the db_owner role for the user - it works.

我收到的错误是:

无法初始化客户端数据库，因为表'tblApplications，tblApplicationConfiguration，tblApplicationInstallProperties，tblApplicationPreRequisites，tblApplicationTypes，tblComputerApps，tblComputers，tblComputerTypes，tblDriveHWSerials，tblDrives，tblDriveTypes，tblFunctions， tblPermissions，tblRegionLocations，tblRegions，tblRegisteredModules，tblRequestFormats，tblRequestStatus，tblRequestTypes，tblRoles，tblRoleUsers，tblSecurity，tblUsers，tblVehicle，tblVehicleLocationMap，tblVehicleMake，tblRequestProcessingStats(tblProviderProvider)方法 确保可以建立与客户端数据库的连接，并且正确指定了SyncAdapter的SelectIncrementalInsertsCommand属性或SelectIncrementalUpdatesCommand属性.

Unable to initialize the client database, because the schema for table 'tblApplications, tblApplicationConfiguration, tblApplicationInstallProperties, tblApplicationPreRequisites, tblApplicationTypes, tblComputerApps, tblComputers, tblComputerTypes, tblDriveHWSerials, tblDrives, tblDriveTypes, tblFunctions, tblLocationApps, tblLocationComputers, tblLocationIPAddress, tblLocations, tblLocationUsers, tblPermissions, tblRegionLocations, tblRegions, tblRegisteredModules, tblRequestFormats, tblRequestStatus, tblRequestTypes, tblRoles, tblRoleUsers, tblSecurity, tblUsers, tblVehicle, tblVehicleLocationMap, tblVehicleMake, tblRequestProcessingStatus, tblDriveStatus, tblVideoViewTypes' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Make sure that you can establish a connection to the client database and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly.

db_owner角色发布后，我将无法使用它.

I am not able to use the db_owner role when its released.

推荐答案

在Sync Framework中有两种类型的数据库提供程序，即本地数据库缓存项目项使用的脱机提供程序(SqlCeClientSyncProvider/DbServerSyncProvider)和协作/对等提供者(SqlSyncProvider/SqlCeSyncProvider).

there are two types of database providers in Sync Framework, the offline provider (SqlCeClientSyncProvider/DbServerSyncProvider) which is used by the Local Database Cache project item and the collaboration/peer-to-peer provider (SqlSyncProvider/SqlCeSyncProvider).

脱机提供程序不会使用scope_xxx表，因为您不会找到它.

the offline providers dont use the scope_xxx tables as such you wont find it.

假设通过向导配置同步时使用了默认的SQL Server更改跟踪，请尝试将VIEW CHANGE TRACKING权限授予用于连接数据库的帐户.

assuming you used the default SQL Server Change Tracking when you configured the sync via the wizard, try granting VIEW CHANGE TRACKING permission to the account you're using to connect to the database.

这篇关于需要学习如何在SQL Server数据库上设置适当的权限以允许通过Sync Framework 2.1进行同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！