none
Failed to execute the command BulkUpdateCommand - SQL 2008 RRS feed

  • Question

  • 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
    Thursday, September 15, 2011 6:18 PM

Answers

  • if its running fine in Sql 2005, you can actually specify during provisioning if you want to use the bulk procedures or not. it will default to using bulk procedures if you're using Sql 2008.
    • Marked as answer by Jason_Keith Tuesday, September 20, 2011 1:44 PM
    Tuesday, September 20, 2011 2:46 AM
    Moderator

All replies

  • For now we have given the application SQL user account ddladmin rights. We have this currently in development environment but we were hoping to not move it up the chain through testing and production.
    Wired4This
    Friday, September 16, 2011 2:37 PM
  • if its running fine in Sql 2005, you can actually specify during provisioning if you want to use the bulk procedures or not. it will default to using bulk procedures if you're using Sql 2008.
    • Marked as answer by Jason_Keith Tuesday, September 20, 2011 1:44 PM
    Tuesday, September 20, 2011 2:46 AM
    Moderator