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