Securing access to SQL Server data

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!

---

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.

---

A good way to do this might be to author stored procedures that fulfill all of your needs and grant all the users execute only privileges.

If you grant full read access to your users, then yes its going to be difficult to prevent them from what your talking about.

---

As mentioned by others, you can create a SQL account for your application. This will disallow any outside access from anything other than your application itself. Of course, this would mean that access to your application itself would have to be restricted. This is also easily accomplished by limiting read access to the executable to an active directory group which contains users whom get access. Further to that, you application may also use active directory hooks to determine whether to allow write access for those users who need it.

That all being said though.... your biggest problem will be the physical security. If just one user in the authenticated group is untrustworthy, then a certain acceptable usage policy, HR employee screening, and user environment checks and balances must be in place. A proper workstation deployment policy will also be good here, allowing you to restrict removable drive usage, logging all access, connectivity details etc etc.

It's more about the operational environment at this point if you can't trust the user.

---

Users could access SQL tables using linked table in Access or external data query in Excel, however the permissions are controlled by the SQL server, which means the users have read-access, they will be able to access data in read-only mode.