SQL Server Users and Roles

[raamay]

We can define Users and Roles for datbases and tables in SQL server but i wonder how this can be implemented in real time applications developed in platforms like VB, VB.Net etc..

I mean when we create applications, we do create the login form to enter the application. Once a user is inside the application, he/she should be having different privieleges. Some users can only view the informations and some can add/edit/delete informations through the application. So, i wanna know how this is done?

[code green]

Create a table in the database with user ids and their various privileges.
When a user logs on, query this table to get their current privileges into an array.
Then in the application layer control via code which pages or features they are allowed to access based on the values in the array.

Changing the DB privileges directly seems unnescessary

[raamay]

i am not sure how the prieveleges are stored in the database.

[code green]

Create a table in the database with user ids and their various privileges.
i am not sure how the prieveleges are stored in the database.

Don't worry about the correct terms for database privileges.
What I mean is a table with boolean values to indicate whether a user can
SELECT, INSERT, UPDATE, DELETE etc
ie

privieges_table
user_id  select  insert  update  delete
001         1         1       0         0
002         1         0       0         0
003         1         1       1         1 
004         0         0       1         0

etc

You would also need an administator interface that allows only you to modify this table

[ck9663]

The only catch with Code Green's suggestion is that you have to connect to your DB using a common user name which might be difficult trace later on. The good part is you have more control on the application side.

If you still want to use the user name that you actually defined inside the DB, you can actually use that as parameters on your connection string.


-- CK