1

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.

1 Answer 1

1

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

4
  • Thanks, let me quickly try this Oct 5, 2020 at 15:50
  • @AndleebHasan another thing to try - it may be possible to actually map SBA_user to Windows-auth login, in the Linked Server security settings. You know section where you do login mapping Oct 5, 2020 at 16:03
  • I was under the impression that whenever SQL needed to access file while using a SQL Login, it uses the SQL Service account to do it... If you can, you could try to give access to your excel spreadsheet to the SQL service account Oct 5, 2020 at 16:21
  • i tried to did that but i think i have less reputation 'Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score.' Oct 6, 2020 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.