Today I set out to produce a list of who has access to each report so I could get this audited by the business management. I didn’t think it would take long. Download a couple of scripts, evaluate them, pick a good one, execute it, and on with the next job. After all, this is crucial stuff. There’s no point agonising over reducing users’s rights in the database engine to the minimum possible if you then go and give them access to reports that summarise all the data in a readable, saveable, printable form. Clearly then, security auditing of Reporting Services must be a common practice. Or so I thought.
Well, I found bits of the puzzle but I didn’t see anything packaged up and ready to go. I’m sure there are tools you can buy but I kind of resent paying serious money for a product that basically just queries my own data. I prefer source code where I can see what’s going on and learn something new.
So I pulled the pieces together, solved a few problems myself and came up with a stored proc that goes through Reporting’s security records for each report, resolves any domain groups recursively down to lists of users, and lists it all out. If you have a look through, you’ll see Reporting doesn’t make this security data easy to get hold of. Also, I had to add a caching mechanism for domain group membership because it took ages to query the domain that many times.
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
David Wimbush 