Thursday, 3 March 2016

Bulk Insert Permissions in MS SQL Server

In order to be able to execute bulk operations you need to certain level of privilege both on the database (table) you are performing the operation against and the instance you are performing the operation on.

If you don't have the necessary permissions you will get an error similar to the following: Msg 4834, Level 16, State 4, Line

You do not have permission to use the bulk load statement.

This is what one of my users got earlier today when trying to perform a BULK INSERT. To be able to do this successfully they will need the ability (permission) to INSERT records into the table in question.

In our case the following script will do the trick:
USE orderdemo
go
GRANT INSERT ON dbo.orders_BULK TO bulktest
go
They will also need Administer Bulk Operations permission, you can give that permission by executing the following, this is a server level permission:

USE master
go
GRANT ADMINISTER BULK OPERATIONS TO bulktest

No comments:

Post a Comment