使用Micorosoft Sync Framework和批量过程同步两个sql数据库需要哪些权限

Question

我用C#编写了一个程序，使用Microsoft Sync Framework同步两个sql数据库。在成功地配置服务器和客户端之后，我在启动同步的客户端上设置了以下权限：

GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.My_Table to my_role
GRANT DELETE ON [dbo].[My_Table_tracking] TO [my_role] AS [dbo]
GRANT INSERT ON [dbo].[My_Table_tracking] TO [my_role] AS [dbo]
GRANT SELECT ON [dbo].[My_Table_tracking] TO [my_role] AS [dbo]
GRANT UPDATE ON [dbo].[My_Table_tracking] TO [my_role] AS [dbo]
GRANT DELETE ON [dbo].[scope_info] TO [my_role] AS [dbo]
GRANT INSERT ON [dbo].[scope_info] TO [my_role] AS [dbo]
GRANT SELECT ON [dbo].[scope_info] TO [my_role] AS [dbo]
GRANT UPDATE ON [dbo].[scope_info] TO [my_role] AS [dbo]
GRANT DELETE ON [dbo].[scope_config] TO [my_role] AS [dbo]
GRANT INSERT ON [dbo].[scope_config] TO [my_role] AS [dbo]
GRANT SELECT ON [dbo].[scope_config] TO [my_role] AS [dbo]
GRANT UPDATE ON [dbo].[scope_config] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_selectrow] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_update] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_updatemetadata] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_selectchanges] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_insertmetadata] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_insert] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_deletemetadata] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_delete] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_bulkupdate] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_bulkinsert] TO [my_role] AS [dbo]
GRANT EXECUTE ON [dbo].[My_Table_bulkdelete] TO [my_role] AS [dbo]

我使用了以下资源将上面的脚本放在一起：

• 需要了解如何在SQL Server数据库上设置适当的权限以允许通过Sync Framework 2.1进行同步的答案
• Microsoft Sync Framework的最低数据库权限要求

但是，当我运行程序时，会记录以下错误：

此SqlParameterCollection 不包含ParameterName为"@changeTable"的SqlParameter

将执行查询的用户映射到内置的db_owner角色后，程序工作得完美无瑕。

有什么方法可以比较授予my_role和db_owner的有效权限吗？如何使用sql server调试权限问题(ssms中显示的sql server日志没有显示任何与我的问题相关的内容)？或者：需要哪些确切的权限才能使Microsoft Sync Framework通过批量过程同步两个数据库？

Answer 1

有没有办法，我可以比较授予的有效权限my_role和db_owner？

要查看您角色的成员拥有哪些权限，您应该模拟您角色my_role的成员用户，并检查其权限，如下所示：

execute as user = 'my_user_member_of_my_role';
select *
from sys.fn_my_permissions(null, 'database');
revert;

然后检查db_owner有什么权限：通过模拟db_owner角色的某个成员来执行同样的操作，或者如果您是sysadmin，则跳过模拟：

execute as user = 'my_user_member_of_db_owner'; -- skip it if you are sysadmin
select *
from sys.fn_my_permissions(null, 'database');
revert; -- skip it if you are sysadmin

如何调试sql server(sql serverssms中显示的日志没有显示任何与我的问题相关的内容)？

您应该意识到这个错误：

中不包含ParameterName为"@changeTable"的SqlParameter此SqlParameterCollection

不是SQL Server错误。

所以这是程序的错误，请使用编程环境进行调试。

或者：需要哪些确切权限才能进行Microsoft同步框架用批量过程同步两个数据库？

您应该执行哪些确切的代码？

如果你在这里登记你想运行的命令，我会给你写所有必要的权限

第页。S.如果使用BULK INSERT，则应具有ADMINISTER BULK OPERATIONS服务器级别的权限。此权限未授予db_owners。因此，如果是问题所在，则无法通过将用户添加到db_owner数据库角色来解决此问题。

Answer 2

负责批量操作的存储过程使用用户定义的表类型，该表类型以要同步的表命名，例如Mytable_BulkType。要执行这些过程，用户需要对表类型具有以下权限：

GRANT CONTROL ON TYPE::[dbo].[MyTable_BulkType] TO [my_role] AS [dbo]