I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me.
But for the users who do not have SA privileges they are getting an error.
Could some one assist me on this, how the users can execute the stored procedure with my credentials (SYSADMIN)?
create procedure [dbo].[readexcel] (@filepath varchar(max)) as declare @Statement varchar(max) --set @Statement = EXECUTE('select * FROM OPENROWSET(' + '''Microsoft.ACE.OLEDB.12.0'',' + '''Excel 12.0;Database=' + @filepath +''',' + '''SELECT * FROM [Sheet1$]'')');
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server