sql server bulk insert permisisons

Question

if the login is a member of the db_owner on the master database, does it need explict permisions for bulk insert

or does the role db_owner for master already include bulk insert operation permissions.

Answer 1

To perform BULK INSERT or OPENROWSET(BULK) You need to have the server permission ADMINISTER BULK OPERATIONS or be a member of the fixed server role bulkadmin.

Normally, there would not be any members in db_owner in master outside those who are members of sysadmin. But in any case, permissions or role membership on database level, does not give you server-level permissions, and that is what is needed here.

Answer 2

In Azure SQL Database, grant ADMINISTER DATABASE BULK OPERATIONS to the principal in the context of the desire database:

GRANT ADMINISTER DATABASE BULK OPERATIONS TO testuser;

The user will also need INSERT permissions on the target table. These Azure SQL Database permissions are detailed in the BULK INSERT documentation under the permissions section.

https://stackoverflow.com/questions/54836106/permission-issue-on-bulk-insert-in-azure-sql-server

Answer 3

there is a stored procedure that performs bcp, when I login to the sql server using the login which has db_owner permissions on all databases including system, the stored procedure is able to get executed without any need for explicit permissions for bulk operations.

I was wondering how this is possible.

thanks so much for your reply.

Answer 4

Hi @NeophyteSQL ,

As following illustrator, you can see db_owner role has all permissions in the database, and the permission of db_owner role including ADMINISTER DATABASE BULK OPERATIONS.

In order to execute BULK INSERT, the user must be granted ADMINISTER BULK OPERATIONS permission. This can be done either by granting the permission directly to the user or by adding the user to the bulkadmin role.

Grant a user ADMINISTER BULK OPERATIONS permission:

 GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\USER]  

Add a user to the BULKADMIN role:

 ALTER SERVER ROLE [BULKADMIN] ADD MEMBER [DOMAIN\USER]  

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

This explains why the stored procedures running bcp ran successfully, because the db owner includes bcp permissions . It is clear now, thank u al