sql server - Cannot bulk load. Operating system error code 5 (Access is denied.)

Question

For some weird reason I'm having problems executing a bulk insert.

BULK INSERT customer_stg
FROM 'C:UsersMichaelworkspacepydbdataandrew.out.txt'
WITH
(
    FIRSTROW=0,
    FIELDTERMINATOR='',
    ROWTERMINATOR='
'
)

I'm confident after reading this that I've setup my user role correctly, as it states...

Members of the bulkadmin fixed server role can run the BULK INSERT statement.

I have set the Login Properties for the Windows Authentication correctly (as seen below).. to grant server-wide permissions on bulkadmin

_{(source: iforce.co.nz)}

And the command EXEC sp_helpsrvrolemember 'bulkadmin' tells me that the information above was successful, and the current user Michael-PCMichael has bulkadmin permissions.

_{(source: iforce.co.nz)}

But even though I've set everything up correctly as far as I know, I'm still getting the error. executing the bulk insert directly from SQL Server Management Studio.

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:UsersMichaelworkspacepydbdataandrew.out.txt" could not be opened. Operating system error code 5(Access is denied.).

which doesn't make sense because apparently bulkadmins can run the statement, am I meant to reconfigure how the bulkadmin works? (I'm so lost). Any ideas on how to fix it?

Answer 1

This error appears when you are using SQL Server Authentication and SQL Server is not allowed to access the bulk load folder.

So giving SQL server access to the folder will solve the issue.

Here is how to: Go to the folder right click ->properties->Security tab->Edit->Add(on the new window) ->Advanced -> Find Now. Under the users list in the search results, find something like SQLServerMSSQLUser$UserName$SQLExpress and click ok, to all the dialogs opened.