Sorry about my late answer - was off-site.

Well, I thought that adding a job to the sql agent would be the best in this case, but you are right. Event driven would be the best where the event you'd want would be any change to a users role. I haven't gotten in to mod development yet so I haven't read up on what options are available in regards to events handling.

I did play around with it a bit though in the query analyzer ... maybe there are bits here you can use.

Did a script to list all users who are member of role1 AND role2 WHERE role2 is not expired (That is actually a quite important detail to include):

select ur.userid

from userroles ur1, roles r
where r.roleid=ur.roleid
and rolename='role2'
and expiredate<Now

and exists (
select ur2.userid
from userroles ur2, roles r
where ur2.userid=ur1.userid

and r.roleid=ur2.roleid
and rolename='role1')

delete from userroles where to_char(userid)||' '||to_char(roleid) in (

select to_char(ur.userid)||' '||to_char(roleid)

from userroles ur1, roles r
where r.roleid=ur.roleid
and rolename='role2'
and expiredate<Now

and exists (
select ur2.userid
from userroles ur2, roles r
where ur2.userid=ur1.userid

and r.roleid=ur2.roleid
and rolename='role1'))