Reporting security audit

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.

Feel free to use the code, which you can download here.

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

Published Tuesday, December 1, 2009 10:06 PM by DavidWimbush

Comments

# re: Reporting security audit

That is exactly what I was looking for!

It has saved me a lot of work.

Thanks!

Monday, March 15, 2010 4:06 PM by DavidBainbridge