The question is whether the triggers and constraints should be fired or not. If so, specifying FIRE_TRIGGERS and CHECK_CONSTRAINTS would prevent the need to alter the table. (The ALTER is disabling the triggers and constraints so that the bulk insert will run more quickly.)
When triggers and constraints are disabled, they are disabled for everyone, not just the bulk process. (Otherwise it would not be necessary to ALTER the table.) So that does leave a window of time when other operations also are not having these features enforced.
One option is to run a process as:
- Stored procedure to Disable Triggers and Constraints
- BULK INSERT
- Stored procedure to Enable Triggers and Constriants
The stored procedures could do the needed work (running as dbo) without giving the rights to the user doing the bulk insert. This opens the window of the disabled time period slightly wider, but not too bad.
An alternative could also be to BULK INSERT into a staging table that has no triggers and constraints, then use a stored procedure to move the data from the staging table into the target table.