BULK INSERT through Service Broker - strange permissions problem

• Pete Bishop

  Default port

  Points: 1464

  More actions

  December 1, 2016 at 9:23 am

  #332481

  Can anyone help with a strange issue?

  I have a database enabled for service broker containing a stored procedure which performs a BULK INSERT to a local table. The service broker queue has the "Activation execution context" set to "Owner".

  If the owner of the database is "sysadmin", everything runs fine. If the owner of the database is changed to a less privileged login (but is still a member of "bulk_admin") I get the error "You do not have permission to use the bulk load statement.".

  What additional permission is needed to make this work? I thought "bulk_admin" was sufficient? I've even tried "ADMINISTER BULK OPERATIONS" (just in case). Clearly something is being lost.

  Any ideas?

  Thanks.

  UPDATE

  If I run "SELECT permission_name FROM sys.fn_my_permissions(NULL, 'SERVER')" while logged in as the database owner I get:

  CONNECT SQL, ADMINISTER BULK OPERATIONS, VIEW ANY DATABASE

  If I run the same command through service broker I get no results at all. If the database is owned by the "sysadmin" login, the permissions are identical.