First I guess we had best start with definitions right? The permission Administer Bulk Operations and the role bulkadmin are required (one or the other) to perform bulk imports. Nothing more, nothing less. There is a really nice comparison of the two here.

So as with all permissions we only grant them if there is an actual need right? And the best practice of least privilege says that if someone has to be able to do a bulk load on a table then we should grant the bulk load to that one table right? There’s the rub. Bulk admin permissions are at the instance level and are not granular in any way. Ie you can’t grant it specifically to a single database or table. It’s all or nothing.

Now at first glance that seems like a real problem. But if you think about it for a bit longer it’s really not such a big deal. In order to perform a bulk import you actually have to have three permissions (at minimum).

• CONNECT – Have to get into the database in the first place right?
• INSERT – Yes, you actually have to have permission to put data into the table in order to do a bulk insert, just like a regular insert.
• ADMINISTER BULK OPERATIONS – Last and in my opinion least.

Continue reading on SQLStudies.com.