How to restrict some users to update some tables under some criteria?

Q. How to restrict some users to update some tables under some criteria?

A. If you administer SQL server database, you know that you can grant\deny user updating some tables, but what if you wish to grant\deny user updating some tables when just column equal some value, scenario like that

Table Course (ID, CourseName)

Table Student (ID, Name, CourseID, ….)

Sure, the most common example we give when we teach someone the introduction of databases especially relationships, so if you don’t know it; in brief Student has right to have some courses and the relationship is one-to-many; course-took by- many students.

Going on our scenario, data entry1 responsible for insert\update\delete all students take C# course, data entry2 responsible for insert\update\delete all students take Java course

Clear, isn’t it?

What you should do is to create 2 users dataentry1, and dataentry2 and to create 2 database roles CSharpController, and JavaController.

To create user using SQL Server Management Studio [URL]

To create Database Role using SQL Server Management Studio [URL]

Assign dataentry1 to CSharpController role, and dataentry2 to JavaController, you can assign more users to proper role as you see.

Next step; when user does insert\update\delete operation on Student table, users belong to CSharpController have right if and only if CourseID = 1, and the same for JavaController if and only if CourseID = 2

So, let’s write some code or script; yes, trigger to do those validations

create trigger ManageDML_Student on student for insert, delete, update AS --here to get column value and assign it variable to check its value later IF IS_MEMBER ('CSharpController') = 1 && --value == 1--   commit transaction else rollback transaction

Thanks :)

Acknowledge
I'd like to thank Mohamed Abd ALLAH Hewedy, he helped me through this problem