I have been playing with the Database Roles fro SQL 2005
I have table EMPL_INFO in here has SSN and salary info
I set up the database role EMPLOYEE_DATA and only have First Name and Last Name
EMPLOYEE_VIEW_ALL (all columns)
I have sql login and i assigned role to this
I can do select * from EMPL_INFO and viola only first name and last name displayed great.
However all our web apps use DOMAIN\SERVICE ACCOUNT which i would give db_datareader to but i want to change this
I can set the DOMAIN\SERVICE ACCOUNT to use the database role EMPLOYEE_DATA, EMPLOYEE_VIEW_ALL.
The problems comes is that
Tracey logs in web page (i want her to be able to see EMPLOYEE_VIEW_DATA) but
Is there a way to know that DOMAIN\SERVICE ACCOUNT (im the use tracey so i get all data).
I can do this by DOMAIN\Tracey but i could have 1000 users