使用OpenRowSet读取XML的动态查询手动正常但SP_Execute无法运行

可能的原因： #

• SQL Server服务账户无文件访问权限
  手动执行时，使用的是你当前Windows登录账户的权限读取文件；但通过sp_executesql执行动态SQL时，实际是SQL Server服务运行的系统账户（如NT SERVICE\MSSQLSERVER）去访问文件，该账户通常没有C:\inetpub\wwwroot\app路径的读取权限，导致无法加载XML文件。

• 动态SQL变量类型不匹配
  sp_executesql要求传入的SQL语句参数必须是NVARCHAR(MAX)类型，你定义的@sql是VARCHAR(MAX)，隐式转换可能引发语句解析错误，若路径包含特殊字符还会出现乱码，导致执行失败。

• 缺少BULK操作权限
  执行OPENROWSET(BULK...)需要ADMINISTER BULK OPERATIONS权限，手动执行时你的账户可能已被授予该权限，但通过sp_executesql执行时（比如在存储过程中切换了权限上下文），执行账户无此权限，导致操作失败。

解决方法： #

1. 给SQL Server服务账户添加文件读取权限

   • 打开服务管理器，找到SQL Server服务，查看其运行账户
   • 右键C:\inetpub\wwwroot\app文件夹 → 属性 → 安全 → 编辑 → 添加该服务账户，授予读取权限

2. 修正动态SQL变量类型
   将@sql的类型改为NVARCHAR(MAX)，确保和sp_executesql的参数要求匹配，示例代码：

   :setvar StreamsLocalFolder 'C:\inetpub\wwwroot\app'
   
   DECLARE @sql NVARCHAR(MAX)
   SET @sql = N'SELECT MyFile.BulkColumn FROM OPENROWSET(BULK ''' + 
   $(StreamsLocalFolder) + N'\path.xml' + N''', SINGLE_CLOB) AS MyFile'
   EXEC sp_executesql @sql
   

3. 授予BULK操作权限
   若权限不足，执行以下语句为目标账户授权：

   GRANT ADMINISTER BULK OPERATIONS TO [你的登录用户名];
   

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