Data Roles – Design Pattern for Data Privileges

In general, if you are using this model, you should not use any access method except stored procs and the rights shoud be on the procs themselves and never directly on a table or view. This means no dynamic SQL of any kind (including in procs). That way users who directly access the database can still only do the things they are allowed to do by the application. This is one place where using an ORM would be a disaster. There shoud be no SQL Server logins. Instead the users should be in windows groups that are given rights based on what they are allowed to do. These rights could consist of read rights for a table but no insert/updates or deletes that are not done through executing a stored proc. They would get exec rights only to the procs that their user group should be able to run in the application.

Funnily, while writing the question part of the post, I wrote this:

I can't create subclass for each data entity (object, action, ... etc), but I can create a generic subclass that fits all the objects.

That's it!

Even though the prefab factory can configure the instantiated object on-the-fly, it can only do that when the object is created for the very first time. When it is loaded from the model, it is more or less de-serialized, which meant I had to traverse the model tree... Which was so ugly, I couldn't write such code. Writing a custom subclass to rule them all, I can have it configure itself even when loaded from a serialized data model.

(Final remarks: I didn't want all that typing in the question to go to waste, besides stackexchange promotes Q&A style spreading knowledge kinda thing, so, yay)