OpenRowSet and OpenDataSet without sysadmin rights

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

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.

  • How to deploy or publish Windows app with SQL Server database
  • SQL update query returning an error
  • Ignoring accents in SQL Server using LINQ to SQL
  • SQL Left Join first match only
  • Conditional WHERE clause in SQL Server
  • SQL Server - frequent database backups
  • It’s currently not a linked “server” and the file name can change from month to month.

    Here is my query:

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


    2 Solutions collect form web for “OpenRowSet and OpenDataSet without sysadmin rights”

    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.

    I have same problem and solve it with this instructions

    Shortly: add dword register key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YOUR_MSSQL_VERSION.YOR_INSTANCE_NAME\Providers\Microsoft.ACE.OLEDB.12.0\DisallowAdhocAccess to 0

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.