How limit records returned by SQL Query based upon user group/class

[Bob]

I am developing an ASP.NET application that needs to archive documents and
support the retrieval of them. When the document is stored, the user needs
to be able to indicate whether it is a public document or limited to a
group/class of users. Search/retrieval must only return the result set that
the current user is permitted to see.

What is the best mechanism (assuming IIS and SQL Server with Windows
Authentication setup) for supporting returning a subset of records based
upon the class of user performing the query/search?

Ideally the user would have a single sign-on using their Active Directory
domain/userid and this information would be available to include in the SQL
WHERE clause in some fashion.

Thanks,
Bob

 

[Jacek]

I am developing an ASP.NET application that needs to archive documents and
support the retrieval of them. When the document is stored, the user needs
to be able to indicate whether it is a public document or limited to a
group/class of users. Search/retrieval must only return the result set that
the current user is permitted to see.

What is the best mechanism (assuming IIS and SQL Server with Windows
Authentication setup) for supporting returning a subset of records based
upon the class of user performing the query/search?

Ideally the user would have a single sign-on using their Active Directory
domain/userid and this information would be available to include in the
SQL WHERE clause in some fashion.

Consider using system_user() function to determine name of user who is
already
executing the query.

 

[Bob]

Jacek:

Thanks for the response, but not sure how that helps.

Is the ASP.NET application running with a network id associated with a
service or the current browser user?

How does using system_user() assist in determining what roles the current
user is a member of?

How would you use system_user() in a join to determine group membership?

Thanks,
Bob