By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

SQL Server Users and Roles

100+
P: 107
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?
Mar 25 '09 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
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
Mar 25 '09 #2

100+
P: 107
i am not sure how the prieveleges are stored in the database.
Mar 25 '09 #3

code green
Expert 100+
P: 1,726
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
Expand|Select|Wrap|Line Numbers
  1. privieges_table
  2. user_id  select  insert  update  delete
  3. 001         1         1       0         0
  4. 002         1         0       0         0
  5. 003         1         1       1         1 
  6. 004         0         0       1         0
etc

You would also need an administator interface that allows only you to modify this table
Mar 25 '09 #4

ck9663
Expert 2.5K+
P: 2,878
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
Mar 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.