已赋目录读取权限仍遭拒绝，SQL Server BULK INSERT失败排查

这个问题是权限导致的，但不是你登录SQL Server的域用户权限，而是SQL Server服务运行账户的权限不足。

原因分析 #

• 映射网络驱动器、通过SSMS图形界面恢复备份，这些操作是用你本地登录Windows的域用户身份去访问网络目录，和SQL Server服务进程的身份无关；
• BULK INSERT语句是由SQL Server服务进程直接发起的文件访问请求，使用的是SQL Server服务自身的运行账户，而非你登录SQL Server的账户。如果这个服务账户没有目标网络目录的读取权限，就会触发“拒绝访问”（错误代码5）。

解决步骤 #

1. 确认SQL Server服务账户：
   打开Windows服务管理器（services.msc），找到对应的SQL Server服务（比如SQL Server (MSSQLSERVER)），查看其“登录”选项卡中的账户名称。

2. 给服务账户添加目录权限：
   对目标网络目录\\path_to_network_directory\，同时设置：

   • 共享权限：给SQL Server服务账户添加“读取”权限；
   • NTFS权限：给SQL Server服务账户添加“读取”和“读取及执行”权限。

3. 替代方案（无需修改服务账户权限）：

   • 使用OPENROWSET(BULK...)结合EXECUTE AS指定有权限的域用户，示例：

     EXECUTE AS LOGIN = 'Domain\AuthorizedUser';
     SELECT * FROM OPENROWSET(BULK '\\path_to_network_directory\bulkinsert_test.txt', FORMATFILE='...') AS t;
     REVERT;
     

   • 创建SQL Server代理作业，将作业步骤的运行账户设置为拥有目录权限的域用户，在作业中执行BULK INSERT语句。

4. 注意事项：
   BULK INSERT中必须使用完整的UNC路径（\\server\share\file.txt），不要使用本地映射的驱动器字母（比如Z:\），因为SQL Server服务进程无法识别用户本地映射的驱动器。

内容的提问来源于stack exchange，提问作者AKedzierski