I have a SQL Server 2008R2 question regarding making a single row in an odd way.
situation: I have a student records database in which ~ 100 students might in-process in a given day. They enter a good deal of PII. Unfortunately, they frequently have need to save the data and come back later to update or complete their registration because they need one or another piece of information. At that point, they need to be able to go back to their original incomplete entry and add whatever information they need to add. This might occur at any point during the initial week of the course, depending on their schedule, and the difficulty they have in retrieving whatever information they need to complete the database.
Problem: I have a number of groups of people who need to be able to read and modify the student information. Various staff members need to be able to pull some of this information, course managers typically need to be able to read it for compiling statistical reports, and instructors need to access it all the time to add grades, etc. The students MAY need to access it later in the course to update information, but this is not typical. My main problem is that I need the students to have read and write access to THEIR ROW only, while instructors et al. need to be able to read and write to all the rows. With AD integration, everything works EXCEPT that one student could potentially open another student's "file" (row) and view their personal information.
How can I lock one row to one student while still allowing administrative staff and instructors read and potentially write access? I am a one man show, so I need to automate as much of this as possible, and SQL is not my strong suit. I considered using either certificates (we have our own internal CA) and/or AD userIDs, but I'm not sure how to implement it, or if that is really the best choice.
SQL Server row level security
returns a plethora of results.