To do 'row level' or 'value based' as I call it security in SQL you have to create a view that only returns the records the current user has access to. You can do this two ways, create one view that checks to see if the user is in a certain role like...

SELECT * FROM Customers WHERE CreditLimit <
CASE IS_MEMBER('Admin') THEN 100000
CASE IS_MEMBER('User') THEN 10000
ELSE 1000 END

Or, you could create a view for each user, making that user the owner of the view. When you query the view, don't specify an owner and SQL will use the view owned by the current user, if not found it will use the dbo. view.

BTW: I am just setting this up in the app I work on. HTH.

BOb


>Oracle has a feature called Row Level Security..
>When you can tag a where clause onto each table for different users and limit the access. I.E. So that different users can see different records (hide records from different users or roles). I am wondering if such a feature exists on SQL Server 2000. If so what's it called.
>
>
>
>For reference here is the oracle code:
>--Create COntext namespace
>CREATE OR REPLACE CONTEXT QuestPDM USING SET_APP_ROLE;
>
>REM Procedure to Set Application Roles Called iN login Trigger...
>CREATE OR REPLACE PROCEDURE SET_APP_ROLE(
> p_username in varchar2 default sys_context('userenv','session_user'))
>AS
>BEGIN
>
> --set context username
> dbms_session.set_context('QuestPDM','UserName',p_username);
> --find role
> for x in (select granted_role from user_role_privs) loop
>--set context for each role
> dbms_session.set_context('QuestPDM','RoleName',x.granted_role);
> end loop;
>
>END;
>
>
>--Make Automatic with Logon trigger
>CREATE OR REPLACE TRIGGER QUESTPDM_LOGON
>AFTER LOGON ON DATABASE
>BEGIN
> SET_APP_ROLE;
>END;
>
>--use this to test when logged in with the user...
>select * from session_context;
>
>CREATE OR REPLACE FUNCTION PREDICATE_ARTICLE
>(p_schema in varchar2, p_object in varchar2) return varchar2
>AS
> l_arti_pred varchar2(100);
>BEGIN
>
> IF (sys_context('QuestPDM','RoleName')='PDMLVMH')
> THEN
> l_arti_pred:='artno='||''''||'TIS'||'''';
>
> ELSIF (sys_context('QuestPDM','RoleName')='PDMKENZO')
> THEN
> l_arti_pred:='';
> ELSE
> l_arti_pred:='';
> END IF;
> RETURN l_arti_pred;
>END;
>
>EXECUTE DBMS_RLS.ADD_POLICY ('LVMH', 'ARTICLE', 'ARTICLE_POLICY', 'LVMH', 'PREDICATE_ARTICLE', 'update,select,delete,insert');