Securing access to SQL Server data

Question

I'm building a Windows application that will go against a SQL 2008 database. Some of the table data is very sensitive but some of our users will still need at least read if not read/write access to those tables. We're going to use Windows Security to control their access to the database.

I want to know if those same users can access the data in those tables through something like Excel or MS Access? It's about blocking a bad apple from trying to steal the data inside and walking off with it. If they have read access to the data, though, is there any real way to stop them?

Sorry if this is a SQL Server 101 question, but I'm not finding good answers to the question.

Thanks for the help!

Answer 1

If they have to have read access, then you should pretty much assume they can do what they want with the data. If you render data on the screen, you have to assume someone can write down what they see with a paper and pencil.

With that said, I'd create a service account that has permissions to access SQL Server from your application. Control the access in your app via roles. Do not grant any users access to your database directly - they have to go through your app.

If you try to open Access or Excel and point it to your SQL Server database, they won't have permission to do anything.