stored procedures - Storing DATABASE_PRINCIPAL_ID in sql server table -

we're using stored procedures restrict access of of our database users. need access specific parts of database (not tables/views, specific rows), , sproc should check if user allowed see table rows he's requesting.

to store authorization rules, we're planning use table dbo.authrules that:

|id|userid  |accessfrom  |accessto    | ... =============================================== | 1|       1| 01.01.2013 | 31.12.2013 | ... | 2|       2| 31.05.2012 | 31.12.2015 | ... 

the stored procedure query table check if current user has access requested data. to make clear: cannot use grant permission because need fine-grained access rules down rows in db.

we're not sure userid column. best solution kind of foreign key system view sys.database_principals, there no foreign keys views.

• should store principal_id column of sys.database_principals, without constraint?
• would better store name column instead of principal_id?
• are there other options store reference db user?

q: should store principal_id column of sys.database_principals, without constraint?

a: if evaluating constraint-based solution, don't have choice. however, if willing write trigger, way @ least 'virtually' create constraint. otherwise have reference system sysowners table , isn't possible without hacking.

q: better store name column instead of principal_id?

a: answer have ask what-if question. if principal_id changes? if name changes? updates/deletes? rather have id-based solution.

q: there other options store reference db user?

a: stated in answer#1, don't think there possibilities except creating trigger...