none
permission error

    Question

  • Salam Friends:

     

    I try to run ETL but i see this message

     

    [OLE DB Destination [8408]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Bulk copy failed. User does not have ALTER TABLE permission on table 'ResearchTable.RD7_JournalAuthor'. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or  check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the  bulk copy command.".

     

     

    the use i use have select, insert, and delete permission

    Thursday, November 01, 2007 7:32 AM

Answers

  • ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or  check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the  bulk copy command.

     

    The error message does tell you what to do. So choose one -

     

    Option 1 - Grant the ALTER TABLE permissions.

     

    Or

     

    Option 2 - Use both the FIRE_TRIGGERS and CHECK_CONSTRAINTS hints. When using the OLE-DB destination with fast load, the default settings include TABLOCK and CHECK_CONSTRAINTS. These can be set on the UI form through checkboxes, but you need to manually set FIRE_TRIGGERS. Inteh designer, select the destrination and look in the properties grid (F4 key). Set the FastLoadOptions property to indlude both requried options, e.g.

     

    TABLOCK,CHECK_CONSTRAINTS,FIRE_TRIGGERS

    Thursday, November 01, 2007 11:02 AM
    Moderator

All replies

  • BCP:

    ALTER TABLE permission is required if any of the following is true:

    • Constraints are disabled, which is the default behavior. To keep constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.

    • Triggers are disabled, which is the default behavior. To fire triggers, use the -h

      option with the FIRE_TRIGGERS hint.
    • You use the -E option to import identity values from a data file.

    Note
    Requiring ALTER TABLE permission on the target table is new in SQL Server 2005. This new requirement might cause bcp scripts that do not enforce triggers and constraint checks to fail if the user account lacks ALTER table permissions for the target table.
    Thursday, November 01, 2007 10:06 AM
  • ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or  check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the  bulk copy command.

     

    The error message does tell you what to do. So choose one -

     

    Option 1 - Grant the ALTER TABLE permissions.

     

    Or

     

    Option 2 - Use both the FIRE_TRIGGERS and CHECK_CONSTRAINTS hints. When using the OLE-DB destination with fast load, the default settings include TABLOCK and CHECK_CONSTRAINTS. These can be set on the UI form through checkboxes, but you need to manually set FIRE_TRIGGERS. Inteh designer, select the destrination and look in the properties grid (F4 key). Set the FastLoadOptions property to indlude both requried options, e.g.

     

    TABLOCK,CHECK_CONSTRAINTS,FIRE_TRIGGERS

    Thursday, November 01, 2007 11:02 AM
    Moderator