RE: Record Level Security

From: Alvin Zhao[MSFT] (alvinzh@online.microsoft.com)
Date: 12/31/02 

From: alvinzh@online.microsoft.com (Alvin Zhao[MSFT])
Date: Tue, 31 Dec 2002 05:29:10 GMT

Hi Pat,

John's suggestion is great. You can define a inline user-defined function
that return the rows in the table that are associated with the current
user. After that, deny SELECT permission of the base table to the user and
give EXECUTE permission of the function to the user so that the user can
only view the rows whose User_ID equals to the current user ID. The pseudo
code of the inline user-defined function is like follows:

CREATE FUNCTION fn_CurrentUserRows()
RETURNS table
AS
RETURN (
        SELECT *
        FROM tablename
        WHERE UserID = User_ID(CURRENT_USER)
       )
GO

You can use SELECT * FROM fn_CurrentUserRows() to get all the rows that
the current user has permission to see.

Sincerely,

Alvin Zhao
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.