I have tried several solutions for my application which lets several users upload files to a database.
The users belong to groups and should only be allowed access to their files or the groups files(if 'sharing' is on).
I have tried to give every group their own database, but it gets 'messy' with all the dynamic sql, since everybody logs on ,via a webservice, using the same database account and execute the same stored procedure to first find the database and then find the files in the table.
What i'm thinking about now is a similar solution, but when a group is created this group gets a unique database account which they share in that group to log on to their database.
That way i could set the default database (on the groups account) to the groups database and i don't have to use dynamic sql to find the database etc.
What i find 'messy' with this solution is that there could potentially be very many server accounts; is that a technical problem?
What could be a better solution?