I am not really a database guy. I had to use a localdb for an application that I created using ASP.NET MVC 5. Now, I understand that the type of database can easily be changed by altering the connection string.
What I want to do is for me to be able to select the users (preferably based on their Windows credentials) that can read from the database.
I am using a localdb v11.0. My schema looks like this:
File table:
How can I do this? Typically, a user will directly try to get the file content from the content column.
Also, is there a better way to store files in databases?
If you're wanting to restrict columns, you've got a good use case for creating a view. Grant select permissions on the view and don't give permission to the underlying table.
As far as storing files in the server, Microsoft has a paper called To BLOB or Not To BLOB on that.
Here's their conclusion:
-if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient
-if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient if you use the FILESTREAM attribute you can maintain
-in between those two, it can be a toss up
If you store the binary in the database, look at moving the file to it's own table if you're not accessing it with every call. If you're using a view, you can get columns from both tables.
If you're using filegroups take a look at files and filegroup architecture.