November 5, 2004 at 2:41 pm
I recently built a new database which will be used by sales agents to track and log their transactions. This was orginally built as a stand alone database for a single agent, but demand and interest has hightend and I need to open this up to many agents needing a solution such as this. One of the issues I'm having is how I should impliment the security within the database so that agent1 cannot look at agent2's information. Or should I build a separate database for each agent and host it on one instance of SQL Server? Which could be a administration nightmare if I get 100 or so clients. Any help on this would be greatly appreciated.
demicoq
November 7, 2004 at 11:21 am
What you're talking about is referred to as "row-level security". This is a good introductory article:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
If you have more specific questions once you get going, post back here and I'll be happy to assist you as best I can.
--
Adam Machanic
whoisactive
November 7, 2004 at 3:18 pm
Another thing you might want to do so you could push off to the users a copy if requested into MSDE is setup the entire database in model on a server with a role assigned all the required permissions on objects. Then each time you create a database from that server it sill create based on the model db and it's structure. Then you can assign the user to the right db and assign to the role, thus as long as the securirty model doesn't change you are covered. However row level security methods can do all the same in the same database and may require a bit less or more admin work to ensure they are kept seperate. Performance wise thou the smaller databases are going to be better long term and with row level security you may block potential indexes because you will eventually need one for the security flag.
This is just to give you an option and some concerns you will run into later as it grows. I have done both however except more for limit groups of folks than each person with row level.
November 7, 2004 at 7:47 pm
Thank you for the suggestions, you both brought up some good points.
Demicoq
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply