SQL Server 2008 OPENROWSET 权限问题 [英] SQL Server 2008 OPENROWSET permission issue

问题描述

我在 Windows Server 2008 Enterprise 64 位上使用 SQL Server 2008 64 位企业版.我发现当我在 SQL Server Management Studio 中执行以下语句时，我需要 sysadmin 权限.我正在使用该语句将数据从 Excel 导入数据库表.我的问题是，我担心 sysadmin 权限太高，有什么解决方案可以使用较低权限的权限来实现相同的功能?

I am using SQL Server 2008 64-bit Enterprise on Windows Server 2008 Enterprise 64-bit. I find when I execute the following statement in SQL Server Management Studio, I need sysadmin permission. I am using the statement to import data from Excel to a database table. My question is, I am concerned that sysadmin permission is too high, any solutions to use lower privileged permission to implement the same function?

select * from  OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;DATABASE=C:\mytest1.xlsx',sheet1$)

推荐答案

来自在线书籍 OPENROWSET (Transact-SQL)

用户需要 ADMINISTER BULK OPERATIONS 权限.

还有这里是授予它的条目.这是服务器级别的权限，所以是的，它相当高.

And here is the entry for GRANTing it. It is a server-level permission, so yes, it is quite high.

要尝试降低权限，您可以创建标准链接服务器连接并使用

To try lower permissions, you could create a standard linked server connection and add a login using

EXEC sp_addlinkedsrvlogin 'LINKSERVERNAME', 'false',
    'localuser', 'rmtuser', 'rmtpass'

似乎不需要授予任何特定权限，因此如果您设置链接服务器，则使用映射到每个本地用户的通用 linkedsrvlogin 进行设置是不明智的.设置特定的本地-远程映射以控制本地用户通过链接服务器在远程服务器上的访问(通过 rmtuser 登录).

There does not appear to be any specific permissions required to be granted, so if you set up a linked server, it is unwise to set it up with a generic linkedsrvlogin that maps to every local user. Set up specific local-remote mappings to control the access of a local user, through the linked-server, at the remote server (by the rmtuser login).

这篇关于SQL Server 2008 OPENROWSET 权限问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！