Password Protect A Table

[elmo29]

Is there any way in which I can hide/password protect a table in SQL Server 7.0 or 2000?

I have some information in there which I don't want people messing with, but some bright sparks are going directly into the table on the computer in which the table is on and altering the values to suit them, and not going through the software I have written which has security access. They sometimes need access to the computer and some of the tables, but I don't want them to have access to particular ones.

Is there any way in which I can achieve this in SQL Server? Microsoft Access does a similar thing
Elmo

 

[bperry]

SQL Server doesn't password a table.

But how you would do this would be to set their permissions to the table(s) such that they don't have update access to the tables. But I guess this would be tough since it sounds like they need the permissions when they go through your app.

Another way to (cleanly) resolve this problem would be to put all your code that accesses the database (from your application) into stored procedures. Your client code (VB? ASP?) could call the stored procedures.

What's great about this approach is that the users then need only execute access on the stored procedures, and you can deny them any access to the tables directly. So they can get their work done as long as they go through your application, but otherwise they are out of luck.

--------------
bperry

 

[elmo29]

Most of my code goes through stored procedures. But how do I stop them going to the raw data and not using the stored procedures when they are actually using the computer the database is stored on?

 

[bperry]

You deny them access to the tables (or alternatively don't provide them with any access.) This is done with the DENY command, or through EM.

(I'm assuming that they log on to this machine with their own signon and password.)

An even better approach (probably can't work for you) is the rule we have at our shop: don't let users on the server.

bperry

 

[tlbroadbent]

Users should never have access to a database server. Users should never know the SA password. Developers don't know the SA password in our shop. We, as DBAs, control access. We can grant, revoke and deny permissions on procedures, views, tables and even columns. SQL provides excellent security when understood and properly utilized.

You may want to consider using Application roles to prevent access to objects in database by any software other than the application you've written. See SQL BOL for details. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.