0

I used my personal laptop computer (Samsung RV520) OS: Windows 7 home premium (x64 bit) SQL Server 2012 Express version 11

I encountered an error when execut the code below.

error message: Msg 4861, Level 16, State 1, Line 47 Cannot bulk load because the file "bulk-campaign" could not be opened. Operating system error code 5(Access is denied.). Msg 4861, Level 16, State 1, Line 47 Cannot bulk load because the file "bulk-campaign.Error.Txt" could not be opened. Operating system error code 5(Access is denied.).

 bulk insert project1.dbo.campaign
 from 'C:\Users\admin121\sql-server-2014-laotu\sql2012_class2_20141023\Project1_Bulk_Insert\textfiles\campaign.txt'
 with 
 (
   firstrow = 2,
   maxerrors = 100,
   errorfile = 'bulk-campaign'
   );

this forum post offered suggestion, but I can't find the correct SQL Server name to give permission on the folder.

Please advise how can I find and add correct SQL Server Name to the the file folder.

I did some research online, it suggested to grant server role bulkadmin to the current login will resolve the issue

alter server role [bulkadmin] add member [boglenetwork\admin121];

After granting the role,I still receive the same error message.

my database name is project1, user associated with the database is dbo. when i try to grant dbo additional user role e.g. acessadmin, system prompted me MS SQL server error: 15405

image1 image1 image1 image1 image1

Improve this question

1 Answer 1

Reset to default
1

  1. Run SQL Server Configuration Manager and under SQL Server Services find your SQL Express instance and see what the Log On As is set to. Alternatively, you can right click the server name in SSMS, Facets, then scroll down to "Service Account" to see it.

    If it's the default NT AUTHORITY\NETWORK SERVICE then grant access to a computer account called YOURDOMAIN\YOURCOMPUTERNAME$. You may also need to grant access to the person executing the command if they're logged on using AD.

  2. Regarding your other user-level access issues, if a user is part of the db_owner role as shown in the screenshot they have the highest database-level access possible and no other database roles are needed. And if they're the database owner (separate to db_owner but pretty much the same privilege wise) then you can't grant additional database-level rights nor do you need to.

Improve this answer
4
  • hello Cody thanks for the reply. My SQL Server logon name is NT Service\MSSQL$SQLEXPRESS. on the folder level, I granted the logon name full control to the folder. I granted bulkadmin server role to the logon. I still receive the same error message. I added image 4 and image 5 on the original post for security tab setting on the folder and user role mapping in SQL Server 2012 Express
    – user2061466
    Commented Apr 28, 2016 at 2:09
  • Is an AD user running the query and do they have access to the folder?
    – Cody Konior
    Commented Apr 28, 2016 at 4:28
  • Hello Cody. I used my personal laptop computer. I added hardware and SQL server version to the original post at the top of the message.
    – user2061466
    Commented Apr 28, 2016 at 17:06
  • So are you logging into SQL with Windows Authentication or SQL Authentication? If you are logging on with Windows Authentication does that user have access to the folder/file you're trying to bulk insert?
    – Cody Konior
    Commented Apr 29, 2016 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.