Prevent write operations on FILESTREAM?
c#, filestream, sql-server
Solution
`SqlFileStream` uses normal `FileStream` under the hood, so basically, you are out of the SQL security context. You're using NTFS file permissions.
Easiest solution would be to use Windows Authentication for SQL server and to put same Windows account in db_datareader group in SQL, and to give it read-only permision on UNC share.
Problem
Running `SQL Server 2012` with `FILESTREAM` enabled. Logged on as a user with read-only rights (`db_datareader`) I successfully run the following code. The `filePath` and `transactionContext` are obtained 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: // https://stackoverflow.com/questions/6463343/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 don't want my `db_datareader` users to be able to modify files! Adding files is possible via a stored procedure, which is ok. But from that point, files are considered read-only. How can I make FILESTREAM data read-only? I.e. I want to disallow opening the files with write access!