I am starting development on a SQL Server Client that will allow a user edit and retrieve records that they they create. These records will live in a single database with other users.

Here are the basic requirements:

  1. Based on the users login, control what records they have access to.
  2. Based on the users login, control whether or not they can even use the client.
  3. Based on the users login, control what CRUD operations they can perform.


Are these functions that should be administered on the server side by creating users and roles, or could it be done within the database with the appropriate tables etc. Such as having a table of containing user names, passwords, privilege ratings, etc. Then based on what information is returned from that table, create variables that can be used in a where clause in a select statement to restrict what records are returned.

As I mentioned earlier, I am in the planning stage, so I am not tied into any particular way of thinking.

Thanks,

Bob Holahan