SQL Server: protect database from being changed directly

Question

Our program ships with an SQL Server 2005 database and SQL Server 2005 Express. Usually it installs its own instance of SQL Server 2005 in the client's computer.

Now I have to add some tables whose content should only be updated from within the program. I need to prevent these tables from being changed directly, by using Management Studio for instance.

How can I achieve this? Should I set user permissions? Can I use encryption? I thought of setting my own 'sa' password for accessing the SQL Server instance and use it only from within the program, but that does not invalidate its access through Windows Authentication.

[Edit] Some clarification of what I'm trying to do. The program is a time and attendance program. The employees' clockings are collected from time clocks and saved in the database; once collected, these clockings cannot be deleted and their date and time values cannot be changed. So I need a way to prevent users from messing with these values directly in the database.

Bear in mind that the majority of our customers does not have any experience in SQL, so I need to have these permissions set upon program installation.

[Edit 2] Thank you for your answers, I would like to make two more questions related to this subject:

1 - Can I grant only SELECT permissions to those that access the DB through Windows Authentication?

2 - Is it possible/viable to protect a table against changes through a hash system? Like adding a hash column and calculate a hash for each row, then comparing the row data with the hash to check for changes?

Answer 1

If anyone has sa-level access, you can't prevent this. Regular users should not have sa access though.

You can help insulate it from regular users via grants, e.g. only giving your application user access to INSERT, UPDATE, DELETE (or EXECUTE, if you are using stored procedures instead of direct SQL) and only giving other users SELECT (or no) access.

You could do some other things to detract the casual user, perhaps with trigger checks on insert/update/delete to enforce that those actions are only being done by your application user. I wouldn't recommend it, but you could do it.

Answer 2

You cannot prevent members of the local Administrators group to do anything they like with your database. Any claim to the contrary is snake oil. In this day and age, all it take is one intrepid user to find a way to alter the data (eg. adding himself clock time) and from then on Google will ensure every user interested will find the hack.

The vast majority of users are members of the Administrators group on the machine they use. Even the in the most tight and strict company enforced group policy environment one would not dream of leaving sensitive data on the users computers when the very paycheck of that user is involved.

Setting up a tamper evident data store on a user computer, in a case when the user has an incentive to tamper the data, is just asking for trouble. Store the data centrally, use a web service to collect the data.

Answer 3

Are you able to use SQL Server Compact Edition instead of the Express? It is embedded into the app and is designed for this kind of scenario.

Answer 4

You can add a hash check to your data at a row level so that if it is modified, a tamper check that runs nightly could detect it and raise an alert.