Which permissions are required to synchronize two sql databases using Micorosoft Sync Framework with bulk procedures?

Question

I wrote a programme in C# using the Microsoft Sync Framework to synchronize two sql databases. After successfully provisioning the server and client, I set the following permissions on the client initiating the synchronisation:

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]

I used the following resources to put the script above together:

However when I run the programme, the following error is logged:

An SqlParameter with ParameterName '@changeTable' is not contained by this SqlParameterCollection

After mapping the user executing the query to the built-in db_owner role, the programme works flawlessly.

Is there any way, I can compare the effective permissions granted to my_role and db_owner? How can I debug the permission issue with sql server (the sql server log shown in ssms does not show anything related to my issue)? Or: Which exact permissions are needed to make the Microsoft Sync Framework synchronize two database with bulk procedures?

sepupic sepupic · Accepted Answer · 2017-11-03T14:23:01

Is there any way, I can compare the effective permissions granted to my_role and db_owner?

To see what permissions have members of your role you should impersonate a user that is a member of your role my_role and check its permissions like this:

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

Then check what permissions db_owner has: do the same by impersonating some member of db_owner role, or if you are sysadmin just skip impersonation:

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

How can I debug the permission issue with sql server (the sql server log shown in ssms does not show anything related to my issue)?

You should realize that this error:

An SqlParameter with ParameterName '@changeTable' is not contained by this SqlParameterCollection

is not SQL Server error.

So it's an error of your program, debug it using your programming environment.

Or: Which exact permissions are needed to make the Microsoft Sync Framework synchronize two database with bulk procedures?

What exact code should you execute?

If you enlist here the commands you want to run I'll write you all the necessary permissions to do it.

P.S. If you use BULK INSERT, you should have ADMINISTER BULK OPERATIONS server level permission. This permission is not granted to db_owners. So if it was the issue, you could not resolve it by just adding your user to db_owner database role.

Which permissions are required to synchronize two sql databases using Micorosoft Sync Framework with bulk procedures?

2 Answers