mattypenny

I’m not great at T-sql, and I know that cursors are unfashionable in Microsoft world, but this gives me the output I want, as below:

drop table #ReportPermissions

create table #ReportPermissions
(Path varchar(100),
 Username varchar(100),
 GrantedPermissions varchar(100))


declare
  UserWithPermissionsCursor cursor for
  select -- ,roles.[RoleID]
      rolename
      -- ,users.[UserID]
      ,username
      -- ,catalog.[PolicyID]
      ,path
  FROM [ReportServerRep01].[dbo].[PolicyUserRole],
       roles,
       users,
       catalog
where policyuserrole.RoleID = roles.roleid
and   policyuserrole.UserID = users.userid
and   policyuserrole.policyid = catalog.PolicyID
order by path, username, rolename

declare @rolename varchar(100)
declare @username varchar(100)
declare @path varchar(100)
declare @PermissionsString varchar(100)
declare @Savedusername varchar(100)
declare @Savedpath varchar(100)


open UserWithPermissionsCursor
FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

WHILE 0 = @@fetch_status
BEGIN
 if (@SavedUserName = @username AND @SavedPath = @path)

    set @PermissionsString = @PermissionsString + ',' + @Rolename

 else
 begin
    -- Output the line
    insert into #ReportPermissions
    (Path,
     Username,
     GrantedPermissions)
    values
    (@SavedPath,
     @SavedUserName,
     @PermissionsString)

    -- Reinitialize variables
    set @SavedPath = @path
    set @SavedUsername = @username
    set @PermissionsString = @rolename

 end

 FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

END
close UserWithPermissionsCursor
deallocate UserWithPermissionsCursor

select * from #ReportPermissions