fnUserPrivsExcludingOwnership

create FUNCTION [dbo].[fnUserPrivsExcludingOwnership]
            (
            @ClientUsersID int,
            @ObjectTypesID int
            )

--takes all my roles joins and them on all the role object privs for all objects
--grouping/adding together the "can" rights for each of my roles to see what I can do with this object/task
--thus taking into acct that if at least one of my roles can do X then I can do X 
--Note - this does not take into acct that I may own the object in question - that must be handled elsewhere...

            RETURNS TABLE
            AS
            RETURN
            SELECT 
            OP.ObjectGuid,
            OP.ObjectTypesID,

            CASE     
                WHEN SUM(coalesce(CanView,1)) > 0 
                    THEN CONVERT(bit,1) 
                ELSE CONVERT(bit,0) 
            END canView,
            CASE     
                WHEN SUM(coalesce(CanEdit,0)) > 0 
                    THEN CONVERT(bit,1) 
                ELSE CONVERT(bit,0) 
            END canEdit,
            CASE     
                WHEN SUM(coalesce(CanAdd,0)) > 0 
                    THEN CONVERT(bit,1) 
                ELSE CONVERT(bit,0) 
            END canAdd,
            CASE     
                WHEN SUM(coalesce(CanDelete,0)) > 0 
                    THEN CONVERT(bit,1) 
                ELSE CONVERT(bit,0) 
            END canDelete,
            CASE     
                WHEN SUM(coalesce(CanSecure,0)) > 0 
                    THEN CONVERT(bit,1) 
                ELSE CONVERT(bit,0) 
            END canSecure
            FROM dbo.fnUserRoles(@ClientUsersID,1) UR
            INNER JOIN [dbo].RoleObjectPrivs OP ON OP.ClientRolesID = UR.ClientRolesID
            where @ObjectTypesID  in (0, OP.ObjectTypesID)
            GROUP BY Op.ObjectGuid, OP.ObjectTypesID