SQL Server 2008 OPENROWSET permission issue

Question

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$)

Solution 1

From books online OPENROWSET (Transact-SQL)

The user requires the ADMINISTER BULK OPERATIONS permission.

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'

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

Solution 2

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

Please try with this one

Comments

[George2]

Thanks, I am interested in the linked server solution. Using linked server, what is the lowest permission needed? Could you refer me a guide or tutotial?