you need to decide that the new user is going to copy/inherit the roles of a specific user.
for example:
insert into user_role
select
3, --the userid of the new person who will receive/inherit the roles
r.roleid
from [role] r
where r.userid IN(SELECT userid from u WHERE fname = 'john')--he was userid 1 in your example
Lowell
--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!