So this blog post has a long title, apologies for that. This is an important FileTable issue that you must be aware of before implementing this feature.

Now that SQL Server 2012 is officially released, people are looking to deploy it and take advantage of the many new and improved features. While HADRON is the big news in SQL 2012, FileTables are a great addition to the non-relational capabilities of SQL Server. I have already written some and I will write more about FileTables, but at this point, I need to share the results of some research I did while writing the FileTable chapter in an upcoming book on FILESTREAM. (FileTable is squarely built on FILESTREAM.)

There is a significant issue (a bug based on my analysis, but not yet confirmed by Microsoft) that makes it impossible to provide users with read-only access to the FileTable data. This issue is also discussed on Connect: http://connect.microsoft.com/SQLServer/feedback/details/721349/cannot-prevent-users-with-select-permission-on-filetable-enabled-for-non-transactional-access-to-make-modifications-to-filestream-data. Microsoft closed the issue as “Resolved as By Design” about 2 months ago with a note that it is being researched and will be addressed in an upcoming service release.

This effectively means that if you have a group of users in your environment who should only be able to read the FileTable data, but you have other users who need the modify the FileTable data, you are not able to set this up. Any login or role (i.e. user or group, as FileTable only works with Windows authentication) granted SELECT permission on the FileTable will be able to modify the FILESTREAM data stored on the file share.

The following conditions must be met:

• You have created a FileTable.
• You have enabled full non-transactional access to the FileTable. (Presumably this will be the most common scenario, as FileTables without non-transactional access enabled don’t add much value over FILESTREAM.)
  • If you have enabled non-transactional access at the Read_Only level only, then SQL Server will work as expected and only provide read-only access – but that applies to all users and groups.
• You have created a SQL Server login and user mapping for a user or group that does not have Update permission on the FileTable.
  • This could be because the user has only been granted SELECT permission.
  • It could also be because you have explicitly denied UPDATE permission to that user.

When these conditions are met, the users who only have SELECT permission on the FileTable will be able to open files (using Notepad let’s say) and successfully save those files. They are not able to create new files (the equivalent of inserting a new row in the FileTable) or delete files (the equivalent of deleting a row from the FileTable).

At this time, I am unable to suggest a workaround. So while FileTable security may already be a deal breaker in some environments (details in the book!), this issue creates additional concerns around the security mechanism of FileTable. If your access rights call for all users with any permission to the table to be able to update any FILESTREAM data, then FileTable may work for you “as is.” However, if some of your users should only have read-only permission while others require read/write, FileTable will not work for you at this time.