locked
Security inconsistent with FileTable RRS feed

  • Question

  • 1. Create an additional Windows account, I'll refer to this as Machine\User2.

    2. Add Machine\User2 as a login to the instance.

    3. Create a database, which I'll call test and specify non_transacted_access = Full and give FILESTREAM a directory name of abc

    4. Add Machine\User2 to the test database

    5. Create 2 file tables - TestTable1 and TestTable2

    6. Grant Machine\User2 read/write access to TestTable2

    7. Start | Run and type in \\machine name\mssqlserver\abc while logged into your account and you will see two folders TestTable1 and TestTable2.

    8. Log off and log back on as Machine\User2

    9. Start | Run and type in \\machine name\mssqlserver\abc.  You'll see that there are no folders at all.  But, you can type in \\machine name\mssqlserver\abc\TestTable2 and access files.

    If you connect to the SQL Server, you will only see TestTable2 in the database because of metadata security.  However, when it comes to FileTable, it appears that metadata security is applied differently from security on any other database object.  The directory name of abc isn't an actual Windows share, but is stored as the root of the FileTable namespace within SQL Server.  It appears that when you are looking at FileTable, SQL Server will only display the contents of a folder if you have access to EVERY object within that folder.

    That makes the security model completely inconsistent between the FileTable feature and everything else.  I would expect to be able to see the folders I have access to when I navigate to \\machine name\mssqlserver\abc, just like I can see every database object that I have access to when I navigate to the test database.

    Why have completely different access rules between FileTable and every other database object?


    Mike Hotek mhotek@mssqlserver.com
    Tuesday, November 15, 2011 7:02 AM

All replies

  • Mike,

    Have you filed this as feedback on Connect? I have observed the same behavior.

    SA.

    Sunday, January 22, 2012 12:33 AM