sql security and access to tables only

Hi all,
this is part one of a 2-part problem.
I have an MS SQL db with tables. I want everything secure, with data just written into the db (excluding my SA priveleges to query the db),
EXCEPT. I want to have a table that is open for to our users to extract data. (there is a generic search through the table to extract a string which is changed periodically)

How would I establish the security for my MS SQL db so that all the users are 'write' only. And can 'read' only that one table. (MyDSN: Mytable: column1 column2 column3 column4 column5)
Thanks in advance.

Quote:

---

Originally posted by bbarnum
Hi all,
this is part one of a 2-part problem.
I have an MS SQL db with tables. I want everything secure, with data just written into the db (excluding my SA priveleges to query the db),
EXCEPT. I want to have a table that is open for to our users to extract data. (there is a generic search through the table to extract a string which is changed periodically)

How would I establish the security for my MS SQL db so that all the users are 'write' only. And can 'read' only that one table. (MyDSN: Mytable: column1 column2 column3 column4 column5)
Thanks in advance.

---

EASY WAY. All users share one login.

Create a newuser (in Enterprise manager, open a query windo to the database you want to do this to and select users from the manage window). Assign a password to that user. Then select Logins from the manage window, chosing the username you just created and setting the access attributes by clicking on the appropriate boxes. Change password when someone leaves and notify everyone (left) what the pw is.

Harder way. All users have their own login.

Similar to above except you create a "group" instead of a new user. You'll have to add new users and delete users to/from the group as they come and go.


Let me know if you need the DDL...

[Edited by JHausmann on 08-08-2000 at 01:16 AM]

Dear JHausman,
All that seems ok, except there are a lot of users, so what you propose would not work well. I have 1 MS SQL DB.
2-part problem. lots of data coming in from users, need security for that. So, I want 'write-only' access for all general users, to enter their info into datatables.
Some users (still lots) need to query just one table to retrieve a data string (which is changed on occasion). So, I want 'read-only' access for all these general users. I really want to avoid having any passwords if possible for security sake.
bb.

How are they accessing your server? If it's thru _your_ code, they don't need to know the user name that has read or write access. One application I support has over 1200 users who all log in (connect to the server. They are all have a unique identifier, as part of a RI table, that enables us to see who did what) with _one_ user id.

Secondly, if you're not assigning passwords, what's going to prevent someone from logging on using the "write" user(s) to do whatever they want? A user enabled for "write" access needs to have "read" access, if only to check to see if the record(s) about to be added already exist.