0

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 (\\servername\FileStream\DatabaseFileStreamDirectoryName\PDFReports).

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]
0

I've had to resort to a bit of guesswork, but as far as I can tell, I haven't done anything overly permissive.

I started with a non-privileged test account that's a member of the AD group I created, and I was able to navigate as far as the FileStream directory for the database (\\servername\FileStream\DatabaseFileStreamDirectoryName), but nothing was visible in there.

So, I tried the most obvious approach first:

GRANT VIEW DEFINITION ON PDFReports TO [MyDomain\App Operators]

...Then hit F5 in the VM I was browsing from, and the directory for PDF reports showed right up. I was able to browse in there, and copy some files to the directory.

So I think I'm good, but I'd appreciate anybody piping up if I've done anything bone-headed.

  • I get Cannot find the object 'XYZ', because it does not exist or you do not have permission. instead of PDFReports ,I have used my file table name,please provide steps – kudlatiger Apr 5 '16 at 6:45

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.