Sql-server – sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.)

bulk-insertsql-server-2012

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

Best Answer

  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.

Related Question