none
Differences vs OPENROWSET & BULK INSERT and required permissions? RRS feed

  • Question

  • 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.

    Tuesday, June 4, 2019 10:23 PM

All replies

  • 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


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Wednesday, June 5, 2019 8:22 AM
  • Hi techresearch7777777,

     

    Supplement to pituach's reply :Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 5, 2019 9:09 AM
  • Thank you both for the replies.

    Is it correct to say that running... GRANT ADMINISTER BULK OPERATIONS TO [server_login]

    will give permissions for [server_login] to do both BULK INSERT & OPENROWSET and can't limit to just one of those ?

    Believe BULK INSERT will only be able to run on the DBs that [server_login] has at least Insert permissions on and not on all DBs which is good.

    So then OPENROWSET is more like a regular Select query and when used with the Insert Into command  [server_login] will only be able to Insert into just the DBs that it has at least Insert permissions or something else ?

    Wednesday, June 5, 2019 4:31 PM
  • Let's see if I can straighten out the confusion.

    OPENROWSET comes into flavours: OPENROWSET(BULK) and OPENROWSET('provider'), or just OPENROWSET for short.

    OPENROWSET(BULK) and BULK INSERT both require ADMINISTER BULK OPERATIONS. If you want to use them to insert data into a table, you need INSERT permissions. Some options may also require ALTER permission on the table.
    OPENROWSET without BULK permits you to run a query on a remote data source without a linked server being defined for that data source. No particular permission is required for this form of OPENROWSET, but the server configuration parameter "Ad Hoc Distributed Queries" must be 1, and by default it is turned off. To change the configuration, you need to be sysadmin. This form of OPENROWSET has nothing to do with bulk load, but it is a cousin to OPENQUERY.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 5, 2019 9:09 PM
  • Hi techresearch7777777,

     

    Both  BULK INSERT & OPENROWSET need  ADMINISTER BULK OPERATIONS permission, and if you want to insert data into database,  you also need INSERT permissions.

     

    >>when used with the Insert Into command  [server_login] will only be able to Insert into just the DBs that it has at least Insert permissions or something else ?

     

    Yes , you are right.

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by pituachMVP Sunday, June 9, 2019 1:09 AM
    Friday, June 7, 2019 5:05 AM
  • Hi techresearch7777777,

     

    In order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 11, 2019 1:28 AM