question

NeophyteSQL avatar image
0 Votes"
NeophyteSQL asked HitenBhavsar-MSFT answered

sql server bulk insert permisisons

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you.

actually, the login had effective permisions to administer bulk operations which is why the stored proc could be executed. I neglected to check the effective permissions that includes the server level permisisons, sorry about that.

db_owner role is not sufficient to perform bcp.

I have tested that.

explicit permisions at server level to bulk insert is necessary to execute the stored proc doing bcp.

thanks you erlandsommarskog and sorry about all the confusion. I neglected to see that the login already had bulk insert permissions at the effective permissions and when that is removed, the execution is no successful

0 Votes 0 ·

Good to hear that everything cleared out in the end!

0 Votes 0 ·
NeophyteSQL avatar image
0 Votes"
NeophyteSQL answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ErlandSommarskog commented

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.



1.png (472.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

getting this error
" Securable class 'server' not supported in this version of SQL Server. azure sql error

0 Votes 0 ·

Don't piggyback on threads. Start a new thread with a question specific to your problem. Permissions in Azure SQL Database is not the same as on-prem or Managed Instance.

0 Votes 0 ·
NeophyteSQL avatar image
0 Votes"
NeophyteSQL answered NeophyteSQL commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NeophyteSQL, it good to know that! If the answers helped, could you please do "Accept Answer"? By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

the login had bulk insert permissions failing which the stored proc execution failed.

also, db_owner is not sufficient to execute bcp operations.

0 Votes 0 ·
NeophyteSQL avatar image
0 Votes"
NeophyteSQL answered

is there any Microsoft documentation to show that db_owner includes bcp permissions

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HitenBhavsar-MSFT avatar image
1 Vote"
HitenBhavsar-MSFT answered


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.