Hello, I am using sync 2.1 syncing SQL Server 2008 with SQL Express 2008. I started off using SQL Server 2005 and SQL Express 2008. Everything was well. Recently my organization upgraded our server to SQL 2008. I noticed that the de-provision/re-provision
created the new bulk objects. That is good as well. All permissions have been given to these objects as well.
The issue I have is when the application runs the BulkUpdateCommand (stored proc 'tblName_bulkupdate') it fails for tables that have an integer primary key. We carefully manage the integer primary key in code to avoid any sync collisions.
The reason for this failure is because the stored proc runs the command "Set Identity_Insert ON".
Our application SQL user account is structured to only have data reader and writer permissions. Looking at security needed to run Set Identity Insert ON it appears you have to be the owner of the table or have ALTER permissions on the table (SET
IDENTITY_INSERT (Transact-SQL)). This is way too much power to give to an application sql account that only needs to insert/update data. By the way, we also manually compare and migrate all sync objects/data from our development environment
to testing environment due to the low powered sql application account.
After talking with some of my co-workers, they suggested the bulk update stored procedure needs to execute using "WITH EXECUTE AS OWNER". That way it could isolate the code running in an escalated mode.
We have come into this problem before with Set Identity Insert ON before, but we got around it by not allowing sync to handle integer PK inserts. We do that manually before sync. That works because the stored procs are specific and one specifically inserts
and the other updates. However in the bulk update, even the update procedure has Set Identity Insert ON. I'm not sure why that's there due to this being an update procedure (maybe there for accidental inserts from the MERGE command?).
Is there any way to modify sql security, stored procs, or sync to allow for a low powered (data reader/writer) application account to execute the bulk update command without failure?
Thanks, Jason
Wired4This
