Question Details

No question body available.

Tags

sql-server

Answers (2)

December 10, 2025 Score: 2 Rep: 80,583 Quality: Medium Completeness: 100%

bcp.exe, as well as other bulk load methods such as SqlBulkCopy, use a special TDS command called INSERT BULK, not to be confused with BULK INSERT. While the latter requires bulkadmin role, the former is actually just a special INSERT statement.

The syntax for the INSERT BULK is noted here under the section -- External tool only syntax. The client sends this statement, immediately followed by a COLMETADATA TDS token (describing the columns), zero or more ROW tokens for the data then a DONE token. It is not possible to use this syntax directly in a user-supplied SQL statement. For more info, see here.


The permissions for bcp.exe, as well as SqlBulkCopy etc, do require ALTER TABLE permisions in some circumstances:

A bcp in operation minimally requires SELECT/INSERT permissions on the target table. In addition, ALTER TABLE permission is required if any of the following conditions are true:

  • Constraints exist and the CHECKCONSTRAINTS hint isn't specified.
    Disabling constraints is the default behavior. To enable constraints explicitly, use the -h option with the CHECKCONSTRAINTS hint.

  • Triggers exist and the FIRETRIGGER hint isn't specified.
    By default, triggers aren't fired. To fire triggers explicitly, use the -h option with the FIRE_TRIGGERS hint.

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

For SqlBulkCopy these are CheckConstraints, FireTriggers and KeepIdentity options respectively.


On the other hand, the BULK INSERT statement can do file-access on the server, which is either done using SQL Server's own service account or using pass-through delegation, both of which carry some risks. It therefore needs higher permissions than other bulk insert mechanisms, which can only stream data from the client. So as well as the above INSERT and possible ALTER permissions, it also needs ADMINISTER BULK OPERATIONS which is provided by the bulkadmin role.

December 9, 2025 Score: 0 Rep: 5,321 Quality: Low Completeness: 10%

BULK INSERT is a TSQL command that read a filefrom a directory that in in the security scope of the SQL Server service and inserts data into a table.

BCP.exe is an OS command that can do import/export of data file (like a BACKUP), wich require much more security at the OS level and SQL Server.