Is it safe to grant Administer Bulk Operations?
4November 10, 2016 by Kenneth Fisher
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.
So in my opinion it’s pretty harmless. You have to already be able to connect to the database and insert data into the table for this to have any effect. I wouldn’t grant anything unless there’s a specific need, but once there is one I wouldn’t be overly cautious about granting this one.
SQL Studies 
Hey there, Ken. While this statement:
“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.”
is technically true, that doesn’t mean that you can’t effectively grant these types of permissions (or any set of instance-level and/or database-level permissions) in a very granular and controlled manner.
By using Module Signing (i.e. Certificates and ADD SIGNATURE) you can create a Login and/or User as a proxy for the desired set of permissions. The Login and/or User are created from the same Certificate that is used to sign the Stored Procedure / Function / Trigger / etc. That association will grant the permissions assigned to the Login and/or User to any module that is signed from that same Certificate. Meaning, if you only sign a Stored Procedure that bulk inserts into one particular table, then you are “effectively” granting the “bulk operations” / “bulkadmin” permission to just that table.
This approach does allow for very granular control over permissions because the Certificate-based Login and/or User(s) cannot connect / logon or be impersonated via EXECUTE AS. So the permissions granted to the Login and/or User(s) is only ever applied to the modules that are signed with the same Certificate. And so, if you only grant EXECUTE on that Stored Procedure to one User or Role, then you are, again, “effectively” granting that permission to just that User or Role.
To see this in action, I have examples posted in the following DBA.StackExchange answers:
* http://dba.stackexchange.com/questions/62230/what-minimum-permissions-do-i-need-to-provide-to-a-user-so-that-it-can-check-the/103275#103275
* http://dba.stackexchange.com/questions/154878/sql-server-impersonation-is-just-not-working/154884#154884
I do not believe that bulk inserts should ever be allowed on a production database if forward recovery to the point of failure is part of the service level agreement. From the moment a bulk operation takes place until you take a new backup the database is non-forward recoverable. Yes, you can run a differential backup however until it completes your shop is at risk of data loss. When you know exactly when the bulk operation is performed you might be able to schedule the Differential backup but what happens when the bulk operations happen on demand?
I’ve been wrong before but I believe that is only the case if you have the database in bulk recovery mode. The bulk admin permission gives you the ability to use the bulk insert commands but does not determine if minimal logging occurs. It also doesn’t affect recovery.
[…] SQL 2014 Learning Series 1: CONNECT ANY DATABASE SQL 2014 Learning Series 2: SELECT ALL USER SECURABLES New SQL Server 2014 Permissions: CONNECT ANY DATABASE Is it safe to grant ADMINISTER BULK OPERATIONS […]