A SQL-authenticated user SBA_USER is selecting from a View which uses Linked Server which points to an Excel spreadsheet in the I:\Folder directory on a windows server.
Problem I have is that the access to the directory is controlled by Windows and the Login SBA_USER is not a Windows account.
I need a way to give SBA_USER read access to the directory I:\Folder. Please suggest.
Best Answer
One thing you could try is to create stored procedure that would select from a View
When creating stored procedure, use EXECUTE AS clause and set it to a Windows-authenticated account (grant all necessary permissions to this account, required to read from I:\Folder)
Grant execute on procedure to SBA_USER
This way when SBA_USER executes procedure, procedure does select from View, from behalf of the Windows-authenticated account