无法批量加载。操作系统错误代码5（访问被拒绝。） [英] Cannot bulk load. Operating system error code 5 (Access is denied.)

问题描述

出于某些奇怪的原因，我在执行批量插入时遇到问题。

 批量插入customer_stg 
 FROM'C：\用户\Michael\工作区\ pydb\数据\andrew.out.txt'
与
（
 FIRSTROW = 0，
 FIELDTERMINATOR ='\t' ，
 ROWTERMINATOR ='\n'
）
  

阅读

<sub>（来源：

（来源： iforce.co.nz ）</sub>

但是，尽管据我所知一切设置正确，我仍然会遇到错误。

Msg 4861，级别16，状态1，第2行，
无法批量加载，因为文件 C：\Users\Michael\workspace\pydb\data\andrew.out.txt不能打开。操作系统错误代码5（访问被拒绝。）。

这没有意义，因为显然 bulkadmins 可以运行该语句，我的意思是重新配置 bulkadmin 的工作方式吗？ （我很迷茫）。关于如何解决它的任何想法？

解决方案

这是我解决此问题的简单方法：

1. 打开SQL Server


2. 右键单击数据库（要备份）


3. 选择属性


4. 选择权限


5. 选择您的数据库角色（本地或云）


6. 在底部，您将请参阅显式权限表


7. 查找"备份数据库权限，然后单击授予权限。

您的问题已解决。

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

BULK INSERT customer_stg
FROM 'C:\Users\Michael\workspace\pydb\data\andrew.out.txt'
WITH
(
    FIRSTROW=0,
    FIELDTERMINATOR='\t',
    ROWTERMINATOR='\n'
)

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-PC\Michael 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:\Users\Michael\workspace\pydb\data\andrew.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?

解决方案

This is quite simple the way I resolved this problem:

1. open SQL Server
2. right click on database (you want to be backup)
3. select properties
4. select permissions
5. select your database role (local or cloud)
6. in the you bottom you will see explicit permissions table
7. find " backup database " permission and click Grant permission .

your problem is resolved .

这篇关于无法批量加载。操作系统错误代码5（访问被拒绝。）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！