Hey folks,

    I am relatively new to SQL 2000 and have just finished the structure of
my db. The eventual use is to permit remote users an opportunity to view,
update, delete their own records within a single table that contains all
users' records. There is a field for username, which I was hoping could be
used to query/filter each user's records. What I also need to do is only
make each user's personal records visible such that other's records are not
affected by someone else's updates, views, deletes, etc. Is this sort of
thing possible? Can anyone direct me to a primer on getting this set-up?

Thanks in advance,

Dave

What kind of records are you talking about?  Sure it is possible by using a
where clause, but it sounds like you need to think about what you are doing
with the database design before talking technical.  Do you just mean a table
with name address etc?  Then just filter all results by a column that has
user name.

Can you give more insight into what you are actually trying to do?

Show quoteHide quote

Louis,

    I didn't want to get into the gritty details, but here goes. I have
developed the framework for an on-line database (Windows 2003 SP1 with IIS6
and asp) of spider species...some 3,800 different species throughout North
America. UTM coordinates in one of the tables in this database permit
real-time mapping using MapServer.
    The structure of the database is complete (took a good, solid three
weeks) and at the moment I have an Excel template posted on my website for
users. Instead of having to personally deal with data import, update, etc.
via email, what I was hoping to do is permit individual collectors, museums,
and other users an opportunity to maintain their own records. The table in
question has approximately 15 fields e.g. Family, Genus, Species, and that
sort of taxonomic information, as well as a field called "EnteredBy", which
I was hoping could be used to filter, query, etc....sort of an
authentication field whereby clients would only see and be able to update
their own records, while others' remain hidden. I may also wish to permit
some users to view not just their own records, but those of others. There
may be upwards of 20 people who wish to maintain their records in this
table. I would think that a whole series of WHERE queries for each client
would be a real pain in the neck. What I was hoping to do is tie client
permissions (and perhaps a hierarchy of permissions) to particular records,
filtered by the "EnteredBy" field.

Does this get me closer to a better dialogue?

Dave

Show quoteHide quote

This article on row level security may help you
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

I used the technique recently and extended it to cover "office scope"
for managers at each remote location. Instead of putting usernames on
the records. I used a users table and offices table and passed the
foreign key values of the user into the procedures rather than using
SUSER_SNAME()  inside the procedure. It makes it more flexible but
there is added overhead of joining the users table for the views.

hals_left

Now this is interesting and the sort of thing I have in mind. Thanks for the
URL.

Dave

Show quoteHide quote

Change a stored proc to a function
Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt