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.
It’s currently not a linked “server” and the file name can change from month to month.
Here is my query:
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
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 http://www.johnsoer.com/blog/?p=538
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