SQl Server Filestream security access levels

Mark Cooter
Mark Cooter used Ask the Experts™
I have a native C++ application that uses SQL Server 2012 with filestream.  It uses win32 API calls for streaming access e.g. OpenFileStream() to Save/Load the data from the database(s)/filestream.  In the past we have granted sysadmin privileges to SQL logins as we control the SQL instance.  
I would now like to restrict the SQL privileges given to the SQL logins to the least possible.  I have tried SQL Authentication and also Application Roles but neither allow the use of the Win32 API calls to filestream.  Can anyone suggest how I can continue to use the API streaming calls and restrict the SQL access?  My users need to be able a read/write to the application databases and also create, delete, backup, restore user databases.  Many thanks for any help on this....
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

hmm, not sure that I understand your problem..

Why not using integrated authentication? Cause otherwise you will need to manage NTFS permissions and SQL Server permissions for your restore/backup scenarios, which is more complex than using integrated security.

What breaks in your code, if you restrict the permissions? What does "user databases" mean in this context?


Calling OpenFileStream() returns 'Access Denied' message when I use SQL Authentication or Windows Authentication (integrated Security) with an Application Role.  Unless I give my login sysadmin privileges then the filestream access call will not work.  How can I restrict the privileges and still use filestream via the win32 api calls?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial