sql server bulk insert permisisons

NeophyteSQL 241 Reputation points
2021-03-01T20:55:26.44+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,334 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.6K Reputation points
    2021-03-01T22:33:22.543+00:00

    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.


5 additional answers

Sort by: Most helpful
  1. HitenBhavsar-MSFT 381 Reputation points Microsoft Employee
    2021-10-26T13:43:48.58+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. NeophyteSQL 241 Reputation points
    2021-03-02T00:12:56.917+00:00

    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.

    0 comments No comments

  3. CarrinWu-MSFT 6,866 Reputation points
    2021-03-02T03:13:06.08+00:00

    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.
    73157-1.png

    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.


  4. NeophyteSQL 241 Reputation points
    2021-03-02T03:50:13.993+00:00

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