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.

  • Did you see this? Commented May 14, 2017 at 2:46
  • @SqlWorldWide: Thanks, man,yes, I just came from there, I was wondering also if there are other ways, like the ones I listed, if they make sense .
    – MSIS
    Commented May 14, 2017 at 2:49
  • To all: I need to log of for the night, so I will reply to you by tomorrow morning EST. Thanks for any comments/suggestions.
    – MSIS
    Commented May 14, 2017 at 2:51
  • You can definitely create view with each students data and give them read privilege to each student. It all depends how many students you have and how they access this data? How much work will need to happen behind the scene? Commented May 14, 2017 at 2:51

1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.