Nevermind, I figured out a better way to do it:
I altered the geminigetusersforissuealert stored procedure to account for users that are not in a role that allow them to view Private Issues:
ALTER PROCEDURE [dbo].[geminigetusersforissuealert]
@projid NUMERIC(10,0),
@issueid NUMERIC(10,0)
AS
BEGIN
-- issue watchers
SELECT userid,1024 AS watchcode
INTO #temp1
FROM watchissue WHERE projid=@projid AND issueid=@issueid
AND userid NOT IN (SELECT userid FROM usersettings WHERE sname=N'receivealerts' AND svalue=N'N')
-- project watchers
SELECT watchproject.userid, watchproject.watchcode
INTO #temp2
FROM securityschemes
INNER JOIN userroles
ON securityschemes.schemeid = userroles.schemeid
INNER JOIN watchproject
ON userroles.projid = watchproject.projid
AND userroles.userid = watchproject.userid
WHERE watchproject.projid = @projid
AND watchproject.userid NOT IN (SELECT userid FROM #temp1)
AND watchproject.userid NOT IN (SELECT userid FROM usersettings WHERE sname = N'receivealerts' AND svalue = N'N')
AND (
(SELECT isprivate FROM issues WHERE issueid = @issueid) = 0
OR
securityschemes.roles LIKE '%H%'
)
/
--This is the old code
select userid,watchcode
into #temp2
from watchproject where projid=@projid and userid not in (select userid from #temp1)
and userid not in (select userid from usersettings where sname=N'receive_alerts' and svalue=N'N')
/
SELECT a.userid,a.watchcode,b.emailaddress
INTO #temp3
FROM #temp1 a, users b WHERE a.userid=b.userid
UNION ALL
SELECT a.userid,a.watchcode,b.emailaddress FROM #temp2 a, users b WHERE a.userid=b.userid
SELECT a.userid,a.watchcode,a.emailaddress,ISNULL(b.svalue,N'H') AS emailformat,ISNULL(c.svalue,N'Y') AS emailmyself
FROM #temp3 a
LEFT OUTER JOIN (SELECT userid,svalue FROM usersettings WHERE sname=N'emailformat') b ON b.userid=a.userid
LEFT OUTER JOIN (SELECT userid,svalue FROM usersettings WHERE sname=N'emailmyself') c ON c.userid=a.userid
END
