与OPENROWSET& amp; BULK INSERT和所需权限? [英] Differences vs OPENROWSET & BULK INSERT and required permissions?

查看:135
本文介绍了与OPENROWSET& amp; BULK INSERT和所需权限?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

您好,对于特定的SQL登录帐户来说,运行OPENROWSET或BULK INSERT是否正确需要以下内容?...


GRANT ADMINISTER BULK操作到[server_login]  


相信BULK INSERT角度处于SERVER级别,但只能在[server_login]具有插入的DB上运行/写入访问哪些声音是安全的。


但是从OPENROWSET角度来看...... server_login帐户可以在所有数据库上运行吗? 如果是,如何限制只有一个特定的数据库?


用户首先选择OPENROWSET,但如果不可用则可以使用BULK INSERT。


请让我知道如果我关闭和/或遗漏了一些澄清内容。


提前致谢。

解决方案


您好,对于特定的SQL登录帐户来说,运行OPENROWSET或BULK INSERT是否正确需要以下内容?...


GRANT ADMINISTER BULK操作到[server_login]  


相信BULK INSERT角度处于SERVER级别,但只能在DB上运行[server_login]具有对声音进行保护的插入/写入访问权限。


但是从OPENROWSET角度来看...... can server_login帐户可以在所有的DB&NBSP?;如果是,如何限制只有一个特定的数据库?


用户首先选择OPENROWSET,但如果不可用则可以使用BULK INSERT。


请让我知道我是否已经关闭和/或遗漏了一些澄清内容。


提前致谢。


好一天,


我认为你与OPENROWSET和BULK INSERT之类的完全不相关的行为相比有一点混乱。


BULK INSERT很简单在任何查询执行它的用户范围内执行的查询。如果用户X执行BULK INSERT,那么权限需要为用户X


OPENROWSET是一个操作,它可以向远程源开放ROWSET。它可用于从此服务器或完全不同的服务器或甚至其他类型的数据库(如Oracle,MySQL等)提供数据。当我们使用  OPENROWSET时,我们使用连接字符串连接到
远程源,就像连接到远程数据库源的任何应用程序一样。我们在OPENROWSET中使用的参数是用于连接字符串的参数,执行远程查询的用户是在连接字符串中配置
的参数,就像连接到数据库的任何应用程序一样。进行"选择"的用户在远程数据库中与执行OPENROWSET的用户无关,该用户是本地服务器中的用户。


已明确记录"OPENROWSET权限由传递给OLE DB提供程序的
用户名的权限。 - 我们在OPENROWSET中配置的用户是远程数据源中的用户,他需要权限。


为了执行OPENROWSET,调用该命令的用户需要"ADMINISTER" BULK OPERATIONS权限"


有关
OPENROWSET的权限请查看此文档

执行BULK INSERT的权限请查看此文档


Hello, is it correct to say for a particular SQL Login account to run both OPENROWSET or BULK INSERT would need the following?...

GRANT ADMINISTER BULK OPERATIONS TO [server_login] 

Believe the BULK INSERT angle is at SERVER level but only can run on DBs that [server_login] has Insert/Write access to which sounds secured.

But what about from the OPENROWSET angle...can server_login account can run this on all DBs?  If yes how to restrict to only one specific DB?

User is preferring OPENROWSET first but is ok with BULK INSERT if not available.

Please let me know if I'm off and/or missing something in clarifying.

Thanks in advance.

解决方案

Hello, is it correct to say for a particular SQL Login account to run both OPENROWSET or BULK INSERT would need the following?...

GRANT ADMINISTER BULK OPERATIONS TO [server_login] 

Believe the BULK INSERT angle is at SERVER level but only can run on DBs that [server_login] has Insert/Write access to which sounds secured.

But what about from the OPENROWSET angle...can server_login account can run this on all DBs?  If yes how to restrict to only one specific DB?

User is preferring OPENROWSET first but is ok with BULK INSERT if not available.

Please let me know if I'm off and/or missing something in clarifying.

Thanks in advance.

Good day,

I think that you have small confusing comparing to totally unrelated type of actions like OPENROWSET and BULK INSERT.

BULK INSERT is a simple query which is executed in the scope of the user which execute it like any query. If user X execute BULK INSERT then the permission need to be for user X

OPENROWSET is an action which OPEN ROWSET to remote source. It can be used to bring data from this server or from a totally different server or even from other type of databases like Oracle, MySQL, and so on. When we use OPENROWSET we connect to remote source using connection string like any application that connect to remote database source. The parameters we use in the OPENROWSET are the parameters which used for the connection string and the user that execute the remote query is the one which is configured in the connection string like any application which connect to a database. The user which make the "select" in the remote database has nothing to do with the user which executed the OPENROWSET which is a user in the local server.

It is clearly documented "OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider." - the user which we configure in the OPENROWSET is a user in the remote data source and he need the permission.

In order to execute OPENROWSET the user that call the command need "ADMINISTER BULK OPERATIONS permission"

For more information regarding the permissions of OPENROWSET please check this documents and for permission to execute BULK INSERT please check this document


这篇关于与OPENROWSET& amp; BULK INSERT和所需权限?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆