Or if anyone has good links for designing our own?
I don't really have any links to demonstrate but I have had to deal with this and this is how i went about it.
We have a system where there are many service providers and many clients who use the system. Clients apply to service providers to join their system and once approved by the SP they get acess to the info and services provided by that provider
So we have many differnet combinations of clients/service providers.
We implemented it like this.
All connections to the database must go thru a central module. This module provides 3 services.
1. ConnectionString Managment
2. Leak detection
3. User ID Management
When a user creates a connetion the module attaches to its state changed event - then when the state changes to opened the module does 2 things.
1. Records the connection open time and the stack trace of where it was opened (I found this bit of good stuff on code project - unfortanatly I don't rembemer by who) - this list is periodiclly inspected for open connetions with a fingerprint to where the connectio0n
2. set the session context on the user connection by executeing set Context_Info against that connection and passes the current user id
Next. In the database we have a simple view that returns the ids of the service providers for which the user of the current connection has access to. The gist of the view is something like this:
select SP_ID a from SPs inner join PermissionList b on a,spid = b.spid where b.userid = GetCurrentUserID()
(GetCurrentUserID is a fiunction that you need to create to retreive the context_info and convert it to a varchar)
From here on in all data selections are done not against the data table but against another set of views which inner join the base tables sp_ID with the spid from the restriction view