I'm setting up a FileTable to allow users to quickly stuff some PDF report files into a database for later searching via an intranet application (to be built later). Sorting out the necessary permissions is a bit of a challenge, and I'm hitting a few roadblocks.
First, I have confirmed that I (with sysadmin privileges) can add and remove files to the share without any issues, so the FileTable is functional at least.
I've added an end user to an AD group, created a Windows login in SQL Server for this group, mapped it to a database user, and granted that user select, insert, update, and delete permissions on the FileTable. The user has logged out of his computer and logged in again to make sure the group I added him to is in the auth token.
He can access the base filestream share for the instance (
\\servername\FileStream), but when he tries to go deeper to the database-specific directory (
\\servername\FileStream\DatabaseFileStreamDirectoryName), he gets a permission error ("You do not have permission to access..."). Same thing if he tries to access the FileTable path directly (
So, what is the proper way to grant access so that users can browse to the share subdirectory for a specific database, see the directories in there for FileTables, and ultimately browse/access files within the FileTables? It's apparently more than just a user mapping in the database, and select/insert/update/delete permissions on the table, and I'm not finding a whole lot of clear documentation on this so far.
Here's the (slightly anonymized) code I've used for setting this up:
USE master GO ALTER DATABASE AppData ADD FILEGROUP FS CONTAINS FILESTREAM ALTER DATABASE AppData ADD FILE ( NAME = N'FS', FILENAME = N'S:\Filestream\AppData\FS' ) TO FILEGROUP [FS] ALTER DATABASE AppData SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE AppData SET FILESTREAM( DIRECTORY_NAME = N'AppData' ) WITH NO_WAIT ALTER DATABASE AppData SET MULTI_USER GO USE AppData GO CREATE TABLE PDFReports AS FileTable WITH (FileTable_Directory = 'PDFReports') GRANT SELECT, INSERT, UPDATE, DELETE ON PDFReports TO [MyDomain\App Operators]