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?
The stored procedures responsible for bulk operations use a user defined table type which is named after the table that is going to be synchronized, e.g. Mytable_BulkType. To execute these procedures, the user needs the following permission on the table type:
GRANT CONTROL ON TYPE::[dbo].[MyTable_BulkType] TO [my_role] AS [dbo]