USP_BBDW_REPORTSERVERCREDENTIALS_LOAD

create procedure dbo.[USP_BBDW_REPORTSERVERCREDENTIALS_LOAD]
with execute as owner 
as
begin
    -- do work


select
  newid() as [ID],
  p.[name] as [USERNAME],
  cast(1 as bit) as [INSERVICEROLE],
  cast(0 as bit) as [ISREPORTUSER],
  cast(1 as bit) as [ISINCLUDED]
from sys.[database_role_members] drm
inner join sys.[database_principals] p on drm.[member_principal_id] = p.[principal_id]
inner join sys.[database_principals] rp on drm.[role_principal_id] = rp.[principal_id]
where rp.[name] = 'BBAPPFXSERVICEROLE'

union all

select
  [ID],
  [USERNAME],
  cast(1 as bit) as [ISREPORTUSER],
  cast(0 as bit) as [INSERVICEROLE],
  cast(1 as bit) as [ISINCLUDED]
from dbo.[REPORTSERVERCREDENTIALS]

end