I am relatively new to this( Database, general security issues) , so I may be over-complicating this. I have a database containing student grades . I want to design a view in which I restrict access to each student . I can think of 3 ways of doing this: by granting permissions in master database, using access control lists maybe creating groups in AD and by assigning roles, though this would seem to require one role for each student which seems too much work. Can someone please guide me along? Thanks.
If you want every student get access only to the rows reguarding that sudent, you can accomplish this by adding one more field to your students table where for each student you'll be store his AD account, then create a view that will join all you need including your students table and using a filter like this
where ... and user = dbo.students.ad_account
So every student will get access to his rows only. Here USER is a built-in T-SQL function that will return student's AD account. Certainly you should create logins for your students using windows authentication; you do not need to map them individually, it will be sufficient to add one windows group yourDom\STUDENTS where every student is a member of yourDom\STUDENTS