How to limit access to a table

simply put a DENY SELECT for all the users in that database (except the table and procedure owner, eventually).

I can't remember...is there an easier way to do it than going through each individual user or group and denying permissions?  I thought there was, but I can't remember it if there is.

Since it is a different schema, I think you can get away with not giving users explcit select permissions on the tables, but eventhen the db_owner and sa can read the data.

If it helps, the database "A" is connected to our web site through a single user "sa"

Also, could you guys be a bit more specific on how to do this.  I was searching in sql manager and couldn't find anything close ...  Pseudo syntax for the command line would be fine ..

your website is connecting to the database through the sa account?

>If it helps, the database "A" is connected to our web site through a single user "sa"
you cannot deny anything to sa.

you will have to create some other login for this application (which is recommended, anyhow, to avoid using sa as application login !!!!)

once that is done, in the interface, in the table context menu, find manage permissions, add the user you created in that database for the new login, and in the select column click until the symbol is a red cross (which means : DENY).

Ok, I see I shouldn't be using "sa" for the application.  oops!  thanks for that!

> once that is done, in the interface, in the table context menu, find manage permissions, add the user you created in that database for the new login, and in the select column click until the symbol is a red cross (which means : DENY).

Ok, but that sounds like I am denying table access to the application user.  If I do that , will my procedure and function still be able to access the table (insert,update and delete)?

If you deny access to the table to the user that you're going to connect with, I believe it will still be denied if you give that same user exec permissions on the stored procedure.  Deny overrides a grant.  You could use EXECUTE AS or SETUSER (if on 2000) to override this behavior if necessary though.

if the table and the procedure is owned by dbo (which is just fine), the application user is granted EXEC on the procedure and DENY SELECT on the table, the procedure will run just fine.

one more thing if you are holding CC info in a DB encrypt the CC info and secure the encryption key (or pass phrase).

I can't seem to separate the privs between user and procedure, here's what I have..

database A - all tables and the select and update procedure and function
database B - credit card table

new user called "app"
App has access to both database A and B

I am playing with the roles of the credit card table and assigning different combinations to "app" but when the procedures where so does a direct select of the table, when 'select * from databaseB.creditcards' is denied from user "app"  so it seems is the use of the procedure/function

I don't see a way to grant EXEC on the procedures, but it seems unnecessary because they are owned by the dbo in database A, and I have not be denied running them.  The error I get is "denied select from table creditcards"


bcsql - the data will be encrypted as well, thanks

Or if you're on 2005 you can setup a trust between the two databases

alter database dbname
set trust_worthy on

Thanks, I will try these suggestions!

I would like to close this question myself but the link to Accept Multiple Solutions is not visible, why?

The only link available is "Accept as Solution"  -  I would like to split points..  

After I objected to the auto-close, the Accept Multiple solutions link appeared.

Moderator, I think that should be logged as a bug.