Row level filtering (does not need to be secure)

I'm trying to design a system where I can filter (not secure) a users results, the user may or may not pass in a user ID. We typically use middle tier connection pooling with a single identity, so I believe labelling is not suitable.

I think the ideal solution would be...

User to establishes a connection through our application, a user id will be established as part of the connection. A view is created describing what the user is able to access. Preferably the user should not be aware of the view. The user or our application executes a number of select queries.

Note that there may be many users with different filters required connecting at any time.

Direct user updates of the table do not need to be supported.

This topic comes up on the forums from time to time in the context of row level security.

The following whitepaper may also be useful to you:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

It uses labeling but I think your solution will require something like that because you are pushing an id for the user down to SQL Server from the middle tier.

HTH,

-Steven Gott

S/DET

SQL Server