T-sql to show SSRS permissions

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:

Path Username GrantedPermissions
/Data Sources cfcjmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone BUILTINAdministrators Content Manager
/Data Sources/Statszone cfcjmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone cfcjmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfcjmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfcbbuck Browser
/zTest/By far the greatest team report cfcrfaria Content Manager

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

Pic: A Bristol Boxkite at Stonehenge. Glossing Wikimedia: It is a photograph created by the United Kingdom Government and taken prior to 1 June 1957 and HMSO has declared that the expiry of Crown Copyrights applies worldwide (see File:Bristol Boxkite 1911.jpg – Wikimedia Commons)

Advertisements