I want users to be able to download data stored in a table that contains a FILESTREAM column. Each file is typically 200 to 500 MBytes. I however do not want to grant the user SELECT access (specifically SELECT *) to the table. I was able to create a stored procedure that provides the Path / Transaction Context. However if I do not grant select permissions on the table the SqlFileStream() class fails to open due to a permission error.
If I grant the SELECT permission I risk a user performing a SELECT * on the table and watch the server die as it is trying to return gigabytes of information.
I believe I have a solution. I think I can deny select permissions to the table. The only reasonable way to retrieve the data from the FileStream table is through the application. I can use an Application Role which has permission to the table to perform the download. I will try this out, I was hoping to not have to make this more complicated...