Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bulk Insert Permissions

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 5
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



The user will need ALTER table permissions in certain circumstances.


This information is available in BOL but I came across the need to give this permission today and I thought I would share it.



Comments

Posted by cindyumemoto on 17 April 2015

Please help!

I am unable to get past You do not have permission to use the bulk load statement error message in trying to bulk insert a csv file. I have made sure the user has the rights and roles..

EXEC master..sp_addsrvrolemember @loginame = N'primaryDC\SQLServices', @rolename = N'bulkadmin'

GRANT INSERT ON dbo.xyz  TO [PRIMARYDC\SQLServices]

go

--master

GRANT ADMINISTER BULK OPERATIONS TO  [primaryDC\SQLServices]

and the stored procedure has

alter procedure ImportInventoryAdjustmentCSV_sp

with execute as 'primaryDC\SQLServices'

as

and I check

select current_user

select system_user

in the SP, and all says it is using right User and user has the rights? Any idea?

bulk insert xyz

from '\\TRADFTP\ftproot\incoming\TM_Inventory_Adjustment_20150413_170035082.txt'

with (fieldterminator='|'

    ,rowterminator='\n'

    ,firstrow=2)

Thank you in advance...

Posted by cindyumemoto on 17 April 2015

Please help!

I am unable to get past You do not have permission to use the bulk load statement error message in trying to bulk insert a csv file. I have made sure the user has the rights and roles..

EXEC master..sp_addsrvrolemember @loginame = N'primaryDC\SQLServices', @rolename = N'bulkadmin'

GRANT INSERT ON dbo.xyz  TO [PRIMARYDC\SQLServices]

go

--master

GRANT ADMINISTER BULK OPERATIONS TO  [primaryDC\SQLServices]

and the stored procedure has

alter procedure ImportInventoryAdjustmentCSV_sp

with execute as 'primaryDC\SQLServices'

as

and I check

select current_user

select system_user

in the SP, and all says it is using right User and user has the rights? Any idea?

bulk insert xyz

from '\\TRADFTP\ftproot\incoming\TM_Inventory_Adjustment_20150413_170035082.txt'

with (fieldterminator='|'

    ,rowterminator='\n'

    ,firstrow=2)

Thank you in advance...

Posted by cindyumemoto on 17 April 2015

Please help!

I am unable to get past You do not have permission to use the bulk load statement error message in trying to bulk insert a csv file. I have made sure the user has the rights and roles..

EXEC master..sp_addsrvrolemember @loginame = N'primaryDC\SQLServices', @rolename = N'bulkadmin'

GRANT INSERT ON dbo.xyz  TO [PRIMARYDC\SQLServices]

go

--master

GRANT ADMINISTER BULK OPERATIONS TO  [primaryDC\SQLServices]

and the stored procedure has

alter procedure ImportInventoryAdjustmentCSV_sp

with execute as 'primaryDC\SQLServices'

as

and I check

select current_user

select system_user

in the SP, and all says it is using right User and user has the rights? Any idea?

bulk insert xyz

from '\\TRADFTP\ftproot\incoming\TM_Inventory_Adjustment_20150413_170035082.txt'

with (fieldterminator='|'

    ,rowterminator='\n'

    ,firstrow=2)

Thank you in advance...

Leave a Comment

Please register or log in to leave a comment.