Prevent write operations on FILESTREAM?

Question

Running SQL Server 2012 with FILESTREAM . Logged in as a user with read db_datareader ( db_datareader ) I successfully executed the following code. filePath and transactionContext are retrieved as described in storing files in SQL Server 2008 using the filestream option .

 using (var transaction = new TransactionScope()) { // filePath and transactionContext are obtained as described here: // /questions/1464873/storing-files-in-sql-server-2008-using-the-filestream-option using (var stream = new SqlFileStream(filePath, transactionContext, FileAccess.Write, FileOptions.SequentialScan, 0)) { var b = System.Text.Encoding.Unicode.GetBytes("OVERWRITE"); stream.Write(b, 0, b.Length); } } 

I want to use SqlFileStream for performance reasons, but I do not want my db_datareader users to db_datareader able to modify files! Adding files is possible using a stored procedure, which is normal. But from now on, files are considered read-only.

How can I make FILESTREAM data read-only?

those. I want to prevent the opening of files with write access!

Answer 1

SqlFileStream uses a regular FileStream under the hood, so basically you are out of the SQL security context. You are using NTFS file permissions.

The easiest solution is to use Windows authentication for the SQL server and put the same Windows account in the db_datareader group in SQL and give it read-only permission on the UNC share.

Answer 2

write a shell and not implement the Write() method