I checked that first. I don't seem to have any duplicates and there are constraints to prevent it. Because there are no records to due to the 'Not In' quest to join to from the other table. I included the join because it is actually part of a query where I union another. I decided I did not need the extra columns so removed from both sides. it seems to solve my problem, but would sure like to understand exactly why. Here is my complete query as it seems to work this way.

ALTER PROCEDURE [Secure].[RoleSelectAvailableByUserId]
(
	@AppKey uniqueidentifier,
	@UserId uniqueidentifier
)
AS
	SET NOCOUNT ON;
	SELECT
		[Description],
		[Name],
		[Permanent],
		[RoleID] 
	FROM [Secure].[Role]
	WHERE 
		(
			[ApplicationFK] = @AppKey AND
			[RoleID] NOT IN (SELECT RoleFK FROM [Secure].[UserRole] WHERE [UserFK] = @UserId)
			AND [Active] = 1
		)
	UNION
	SELECT
		[Description],
		[Name],
		[Permanent],
		[GlobalRoleId] AS RoleID
	FROM [Secure].[GlobalRole] 
	WHERE
	(
		[GlobalRoleId] NOT IN
		(
			SELECT GlobalRoleFK FROM [Secure].[UserGlobalRole]
				WHERE ApplicationFK = @AppKey AND
					[UserFK] = @UserId
		)
		AND [Active] = 1
	)

>I would have thought you must have a duplicate ug.GlobalRoleFK if the query works ok without the join
>
>>I know this should be obvious to me but today it is not. I cannot see it and am trying to get this sorted so I can move on to something more important.
>>
>>I have a stored procedure to return rows from a roles table that a user does not already belong in. I am getting duplicate rows. Can someone point me to what it is?
>>
>>Global Role table holds all the Roles available to multiple applications. The UserGlobalRole table holds the records for which users are in which role for a specific application. They can be in multiple roles for an application and they can be in the same role for multiple applications. This query is only interested in those roles available that he is not already in for this specific application.
>>
>>

>>
>>	-- This gives me duplicate rows
>>	SELECT
>>		ug.[Active],
>>		ug.[ApplicationFK],
>>		ug.[LastUpdate],
>>		g.[Description],
>>		g.[Name],
>>		g.[Permanent],
>>		g.[GlobalRoleId] AS RoleID
>>	FROM [Secure].[GlobalRole] g
>>	JOIN [Secure].[UserGlobalRole] ug
>>	ON g.GlobalRoleId = ug.GlobalRoleFK
>>	WHERE g.GlobalRoleID NOT IN
>>	(
>>		SELECT GlobalRoleFK FROM [Secure].[UserGlobalRole]
>>			WHERE ApplicationFK = '785df211-4c61-4b6c-8302-9934711bacd7' AND
>>				UserFK = '972C23B2-3FAB-43F3-8946-576F15A138B5'
>>	)
>>
>>
>>	-- This works fine
>>	SELECT * FROM Secure.GlobalRole
>>	WHERE GlobalRoleID NOT IN
>>	(
>>		SELECT GlobalRoleFK FROM [Secure].[UserGlobalRole]
>>			WHERE ApplicationFK = '785df211-4c61-4b6c-8302-9934711bacd7' AND
>>				UserFK = '972C23B2-3FAB-43F3-8946-576F15A138B5'
>>	)
>>
>>