We are trying to leverage FileStream feature in Sql Server 2008 to implement attachment functionality in our application. We use streaming API (SQLFileStream)
to read and write files to Sql Storage from WCF service and service runs under domain account. Unless this service account is given sysadmin privileges on Sql Server , write operation fails.
It is actually the following line which opens the stream for writing fails with “Access denied exception”.
SqlFileStream objSqlFileStream =
1) Service account is given read/write access as well db_owner on the database where file stream table/columns reside
2) Service account is part of database role which has access to the attachment table containing the file stream columns.
3) Service account is given full control to the File Stream folder on the database server
Sql Server is running on Windows 2008 R2
Sql Server build/version: 10.0.2531
Server is up to date on Windows updates.
Note: Read operation is successful without giving sysadmin rights for the accounts. It is only when the stream is opened for write it fails and once sysadmin rights are granted, it works.
I would like to know what permissions are required for the service account to successfully upload the files (except sysadmin rights). It is not possible to give sysadmin rights for the account in production environment.