Monthly Archives: December 2009

Does this folder exist?

Today I tripped over a problem that was new to me. I wanted to find out, in SQL, whether a folder existed in the file system. I knew about using xp_fileexist to check for existence of a file but what about a folder? Well, it doesn’t work on a folder name and there’s no sign of xp_folderexist.

I finally found in a forum that you use xp_fileexist and pass in the imaginary file nul in that folder (eg. to check if the folder ‘C:\test’ exists, check if file ‘C:\test\nul’ exists). There was no explanation but, according to Raymond Chen who knows a few things, NUL is a special, ‘magic’ file name that goes back to DOS 1.0. So some days you learn an old new thing.

Advertisements

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.

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