SQL Server 2005, rights validation

I'm writing an application in C# working with a database, SQL Server 2005. It uses Windows authentification.

The problem is that user should get rights according to information stored in database.

F.e. I store information about different projects in my database. Each project has a leader, each project belongs to a certain field, each field has administrator. So the person, who started some project should be able to modify only that project. Administrator also can start projects but still should be unable to modify other projects. This applies to different select, update and insert cases.

I don't like the idea to check rights in a stored procedure calls, because all of them should be rewritten if the rights for some class of workers is changed.

What is the best way to control access in my case?

Solution

You can't get around the fact that permissions are based on data so you need to check data before an action. That is, in code somewhere.

In a "create" proc/view you need to check that the current user is admin
In an update proc/view, you need to check that the user matches a column

This is simplified but it's what you'll have to do.

For stored procs, you can call another stored proc that will check rights to centralise all checks in just a few places for ease of maintenance.