set quoted_identifier on
go
set ansi_nulls on
go

if exists (	select 	1 
		from 	dbo.sysobjects 
		where 	id = object_id(N'dbo.rpt_ITReportsSecurity') 
			and objectproperty(id, N'IsProcedure') = 1    )
	drop procedure dbo.rpt_ITReportsSecurity
go



create proc dbo.rpt_ITReportsSecurity
as

/*
----------------------------------------------------------------------
Version:	1.0
Date:		01/12/2009
----------------------------------------------------------------------
*/

set nocount on

declare @FolderName		nvarchar(850)
	, @ReportName		nvarchar(850)
	, @ReportPath		nvarchar(850)
	, @idoc			int
	, @xmlfile		varchar(8000)
	, @GroupUserName	varchar(50)
	, @Role			varchar(50)
	, @GroupNoDomain	varchar(50)
	, @i			int
	, @cmd			nvarchar(2000)


create table #perms
(
	GroupUserName	varchar(50)
	, Role		varchar(50)
);

create table #allperms
(
	FolderName	nvarchar(850)
	, ReportName	nvarchar(850)
	, GroupUserName	varchar(50)
	, [Role]	varchar(50)
);

create table #temp_domaingroup 
(
	[output] varchar(8000)
);

create table #group_member 
(
	[Group]		varchar(50)
	, Member	varchar(100)
	, [Role]	varchar(50)
);


-- Loop through reports
declare report_cursor cursor
local forward_only 
for 
select	f.Name as Folder
	, r.Name as ReportName
	, r.Path as ReportPath
from	ReportServer.dbo.Catalog r with (nolock)
	inner join ReportServer.dbo.Catalog f with (nolock) on f.ItemID = r.ParentID
where	r.Type = 2 -- Report
order by f.Name
	, r.Name

open report_cursor

	fetch next from report_cursor 
	into 	@FolderName
		, @ReportName
		, @ReportPath
	

	while @@fetch_status = 0
	begin

		-- Get the user/groups and their roles from the XML

		truncate table #perms;
		 
		set @xmlfile = (select	sd.XmlDescription
				from	ReportServer.dbo.Catalog c
					inner join ReportServer.dbo.Policies p on p.PolicyID = c.PolicyID 
					left join ReportServer.dbo.SecData sd on sd.PolicyID = p.PolicyID and AuthType = 1
				where	c.Path = @ReportPath
					and p.PolicyFlag = 0) --as far as I can tell, this means not a system policy
		 
		exec sp_xml_preparedocument @idoc output, @xmlfile
		 
		insert	#perms 
			(GroupUserName
			, [Role])
		select	GroupUsername
			, Role
		from	openxml	(@idoc, N'/Policies/Policy/Roles/Role',2)
			with	(GroupUsername varchar(50) '../../GroupUserName'
				, Role varchar(50) './Name')
		 
		exec sp_xml_removedocument @idoc;

		-- Loop through users/groups
		declare group_cursor cursor
		local forward_only 
		for 
		select	GroupUserName
			, [Role]
		from	#perms
		where	GroupUserName <> 'BUILTIN\Administrators'
			and [Role] <> 'View Folders Role';

		open group_cursor

			fetch next from group_cursor 
			into 	@GroupUserName
				, @Role
			
			while @@fetch_status = 0
			begin

				-- Strip the domain off the user/group

				set @GroupNoDomain = @GroupUserName
				set @i = charindex('\', @GroupNoDomain)

				if @i > 0
				begin
					set @GroupNoDomain = substring(@GroupNoDomain, @i + 1, 100)
				end

				-- Users in the groups are cached so we only need to query the domain once for each group

				if not exists (select 1 from #group_member where [Group] = @GroupNoDomain)
				begin
					-- Query the domain

					truncate table #temp_domaingroup;

					set @cmd = 'dsquery group -name "' + @GroupNoDomain + '" | dsget group -members -expand';

					insert	#temp_domaingroup 
					exec xp_cmdshell @cmd;

					if exists (select 1 from #temp_domaingroup where [output] like 'dsget failed%')
					begin
						-- It's a user, not a group, so add it to the cache

						insert	#group_member
							([Group]
							, Member
							, [Role])
						values	(@GroupNoDomain
							, @GroupUserName
							, @Role);
					end
					else
					begin
						-- Add the users in the group to the cache
						insert	#group_member
							([Group]
							, Member
							, [Role])
						select	@GroupNoDomain
							, substring(x.Member, 5, len(x.Member) - 4)
							, @Role
						from	(
							select	substring([output], 1, charindex(',OU=', [output], 1) - 1) as Member
							from	#temp_domaingroup 
							where	[output] is not null
							) x;
					end
				end

				-- Add the user or group users and their roles to the permissions list

				insert	#allperms
					(FolderName
					, ReportName
					, GroupUserName
					, [Role])
				select	@FolderName
					, @ReportName
					, Member
					, [Role]
				from	#group_member
				where	[Group] = @GroupNoDomain;

				-- Get the next cursor row
				fetch next from group_cursor 
				into 	@GroupUserName
					, @Role
			
			end

		close group_cursor
		deallocate group_cursor

		-- Get the next cursor row
		fetch next from report_cursor 
		into 	@FolderName
			, @ReportName
			, @ReportPath
	
	end

close report_cursor
deallocate report_cursor


select	*
from	#allperms
order by ReportName;

go