I have a table Users with columns
ID nvarchar(4000)
GroupRank int
Definition nvarchar(4000)
ID can be a userid (in which case groupRank is NULL), a domain group with a rank (in which case grouprank is not null) or a reserved default group called #DefaultGroup.
I need a stored procedure that will:
If ID=SYSTEM_USER, return that Definition
Otherwise - foreach record in Users with GroupRank NOT NULL in order of group rank, if IS_MEMBER(ID) =1, that definition (if any)
Otherwise - the #DefaultGroup definition (if it's there)
Otherwise return NULL.
Is there an easy way to do this?