没有系统管理员权限的 OpenRowSet 和 OpenDataSet [英] OpenRowSet and OpenDataSet without sysadmin rights

问题描述

有没有什么方法可以在 SQL Server 2008 R2 中使用 OpenRowSet 和 OpenDataSet 命令而无需具有 SysAdmin 权限?

Is there any way to use the OpenRowSet and OpenDataSet commands in SQL Server 2008 R2 without having to have SysAdmin rights?

我正在尝试从 Excel 文件加载数据，而我们进行实际加载的服务帐户没有系统管理员权限.

I'm trying to load data from an Excel file and our service account that will do the actual loading can't have sysadmin rights.

它目前不是链接的服务器"，文件名可能会逐月更改.

It's currently not a linked "server" and the file name can change from month to month.

这是我的查询:

SELECT  
  Center,
  Amount 
FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0'
  ,'Excel 8.0;HDR=NO;Database=C:\exceldata.xls'
  ,'SELECT 
      F1 as Center,
      F2 as Amount
   FROM [Sheet1$]')
WHERE Amount > 0

谢谢

推荐答案

您不一定需要 sysadmin 权限，只需要 ADMINISTER BULK OPERATIONS 权限(这是服务器级别权限，但不完全是 sysadmin).

You don't necessarily require sysadmin rights, just the ADMINISTER BULK OPERATIONS permission (which is a server level permission but not quite sysadmin).

如果这不是一个选项，您可以尝试设置链接服务器连接并按照 这篇文章.

If this isn't an option, you can try setting up a linked server connection and follow the method in this post.

这篇关于没有系统管理员权限的 OpenRowSet 和 OpenDataSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！