SET nocount ON 
DECLARE 
	@users TABLE 
	( 
		[user_id] int, 
		name nvarchar(20) 
	) 

INSERT INTO @users VALUES(1, 'name1') 
INSERT INTO @users VALUES(2, 'name2') 
INSERT INTO @users VALUES(3,'name3') 

DECLARE 
	@Rollen TABLE 
	( 
		role_id int, 
		name nvarchar(20) 
	)
 
INSERT INTO @rollen VALUES(1, 'admin') 
INSERT INTO @rollen VALUES(3, 'cmsanager') 
INSERT INTO @rollen VALUES(5,'anonymous') 
INSERT INTO @rollen VALUES(6,'anonymous1') 

DECLARE 
	@userrollen TABLE 
	( 
		f_user_id int, 
		f_role_id int 
	) 
INSERT INTO @userrollen VALUES(1, 1) 
INSERT INTO @userrollen VALUES(1,5) 
INSERT INTO @userrollen VALUES(3,5) 
INSERT INTO @userrollen VALUES(3,3) 
INSERT INTO @userrollen VALUES(2,1) 
INSERT INTO @userrollen VALUES(2,6)



-- Alle Rollen die eine Zuweisung haben
SELECT 
	*, 
	1 AS hasrole 
FROM @users AS users, 
	@userrollen AS userrollen 
WHERE userrollen.f_user_id = users.user_id 
UNION 
-- Rollen die keine Zuweisung haben
SELECT 
	*, 
	0 AS hasrole 
FROM @users AS users, 
	@userrollen AS userrollen 
WHERE not exists
	(
	SELECT 
		top 1 * 
	FROM @userrollen AS userrollen1 
	WHERE userrollen1.f_user_id = users.user_id 
		AND userrollen1.f_role_id= userrollen.f_role_id
	)
