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