Role Nesting

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
                         |
                        boss
                         |
                        worker
                         |
                         subworker

Nested Role

 

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT     ID, Label
FROM         dbo.tblUniqueLabel WITH (NOLOCK)
WHERE     (ID IN
                          (SELECT     dbo.tblUniqueLabel.ID
                            FROM          dbo.tblUniqueLabelMarking WITH (NOLOCK)
                            WHERE      (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.